Database maintenance

It's already split

Do a shrink database on it from SSMS.

Once such:..

SELECT object_name(b.object_id) as obj_name, b.name as index_name, a.*
FROM sys.dm_db_index_usage_stats a
right outer join sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id and a.database_id = 5
where b.object_id > 100
order by user_seeks + user_scans

Select all Open in new window

Do you have a plan for migration?

That means that your transaction is what is filling all your 4GB!

SET NOCOUNT ON

-- dbcc updateusage(0)

-- dbcc shrinkdatabase(LogErrors, 5)

-- dbcc shrinkdatabase(LogErrors, truncateonly)

-- dbcc showcontig with all_indexes, tableresults

-- truncate table CdrArchive_2004_08

-- dbcc shrinkdatabase(radius, truncateonly)

 

-- select fromdate, todate, count(*)

-- from CdrHourlySummary(nolock)

-- group by fromdate, todate

-- order by fromdate desc

 

 

CREATE TABLE #TBLSize

 (Tblname varchar(80), 

 TblRows int,

 TblReserved varchar(80),

 TblData varchar(80),

 TblIndex_Size varchar(80),

 TblUnused varchar(80))

 

CREATE TABLE #TBLSize1

 (Tblname varchar(80), 

 TblRows int,

 TblReserved int,

 TblData int,

 TblIndex_Size int,

 TblUnused int)

 

DECLARE @DBname varchar(80) 

DECLARE @tablename varchar(80) 

 

SELECT @DBname = DB_NAME(DB_ID())

PRINT 'User Table size Report for (Server / Database):   ' + @@ServerName + ' / ' + @DBName

PRINT ''

PRINT 'By Size Descending'

DECLARE TblName_cursor CURSOR FOR 

SELECT u.name + '.' + o.NAME 

FROM sysobjects o INNER JOIN sysusers u ON o.uid = u.uid

WHERE xType = 'U'

 

OPEN TblName_cursor

 

FETCH NEXT FROM TblName_cursor 

INTO @tablename

 

WHILE @@FETCH_STATUS = 0

BEGIN

   INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused)

   EXEC Sp_SpaceUsed @tablename

 

   -- Get the next author.

   FETCH NEXT FROM TblName_cursor 

   INTO @tablename

END

 

CLOSE TblName_cursor

DEALLOCATE TblName_cursor

 

INSERT INTO #tblSize1(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused)

SELECT      TblName, TblRows, 

      Replace(TblReserved, ' KB', ''), Replace(TblData, ' KB', ''),

      Replace(TblIndex_Size, ' KB', ''), Replace(TblUnused, ' KB', '')

FROM #tblSize

 

-- UPDATE #tblSize SET 

--    TblData = Replace(TblData, ' KB', ''), 

--    TblIndex_Size = Replace(TblIndex_Size, ' KB', ''),

--    TblReserved = Replace(TblReserved, ' KB', '')

 

SELECT  CAST(Tblname as Varchar(30)) 'Table',

      CAST(TblRows as Varchar(14)) 'Row Count',

      CAST(TblReserved AS INT) 'Total Space (KB)',

        CAST(TblData as Varchar(14)) 'Data Space',

      CAST(TblIndex_Size  as Varchar(14)) 'Index Space',

        CAST(TblUnused as Varchar(14)) 'Unused Space',

      CASE WHEN TblReserved > 0 THEN

      round((TblIndex_Size + 0.0) / TblReserved, 3) * 100

      ELSE 0 END

FROM #tblSize1

Order by 'Total Space (KB)' Desc

 

 

-- SELECT  CAST(Tblname as Varchar(30)) 'Table',

--    CAST(TblRows as Varchar(14)) 'Row Count',

--    CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)',

--         CAST(TblData as Varchar(14)) 'Data Space',

--    CAST(TblIndex_Size  as Varchar(14)) 'Index Space',

--         CAST(TblUnused as Varchar(14)) 'Unused Space'

-- FROM #tblSize

-- Order by 'Total Space (KB)' Desc

/*

PRINT ''

PRINT 'By Table Name Alphabetical'

 

 

SELECT  CAST(Tblname as Varchar(30)) 'Table',

      CAST(TblRows as Varchar(14)) 'Row Count',

      CAST(TblReserved AS INT) 'Total Space (KB)',

        CAST(TblData as Varchar(14)) 'Data Space',

      CAST(TblIndex_Size  as Varchar(14)) 'Index Space',

        CAST(TblUnused as Varchar(14)) 'Unused Space'

FROM #tblSize1

Order by 'Table'

*/

DROP TABLE #TblSize

DROP TABLE #TblSize1

 

Select all Open in new window

We have SQL Express 2005 and I realized our data file is almost reaching the limit (4GB).

Also I see one table that has about 200 MB used for data and 450 MB for indexes, is that acceptable?

..rename "table1" and then create a synonym for it instead:
CREATE SYNONYM dbo.table1 FOR db2.dbo.table1;

I'm not sure if Access can handle the access to synonymed tables as in the background it uses..

The synonym approach would save you some headache with your existing code as you would not need to add the explicit database name to any SQL code you already have (frontend and backend).

You'll need to rebuild the indexes for that table to have the eventual free space released.

did you check for duplicated indexes in that table

Indeed there is one duplicate index!

CREATE NONCLUSTERED INDEX [IX_OrientNotes_InitIdOrientIDNoteDayMailTs] ON [dbo].[OrientationNotes] 
(
	[Initial] ASC
)
INCLUDE ( [ID],
[OrientationID],
[Note],
[Day],
[Mailing],
[ts]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Select all Open in new window

CREATE NONCLUSTERED INDEX [IX_OrientationNotes_Initial] ON [dbo].[OrientationNotes] 
(
	[Initial] ASC
)
INCLUDE ( [ID],
[OrientationID],
[Note],
[Day],
[Mailing]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Select all Open in new window