It's already splitRight, however I meant here to split the BE itself into multiple files, the way I would do it if this would be an Access database, or even SQL if my FE would be a mdb.
Do a shrink database on it from SSMS.Is that simple as compact an Access DB? what is the command for that?
Once such:..Will check that & let you know.
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
@Pat,Do you have a plan for migration?We have in plan to upgrade the BE but would not like to do that under pressure of reaching the limit, and therefore I'm asking..however regarding the upgrading our FE, its a long story and dont think at the moment we are ready for it.
That means that your transaction is what is filling all your 4GB!I ran the following to get a detailed report of each table size, and then took the sum of all table sizes (3rd column) and got to 3.75 GB, so that means its full of data, no?
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
We have SQL Express 2005 and I realized our data file is almost reaching the limit (4GB).If it possible, upgrade it to SQL Server Express 2008R2. The database limit in this version has increased to 10GB.
Also I see one table that has about 200 MB used for data and 450 MB for indexes, is that acceptable?No, is not. You might have repeated indexes or many covered indexes. Review them. Or if you want, generate a script for that table, including the indexes and post it here so we can review it for you.
..rename "table1" and then create a synonym for it instead:Comparing this approach to what Bitsqueezer suggested (the option of using a view) what are the pros/cons?
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..Just tried and Bit is right, access does not recognize that..
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).Why are you saying there will be more work by creating view's? In both cases I do need to delete the original table and execute what you have posted (either create synonym or create view) while in the front end if I give them the same name as the original table, I dont see anything whats needed to get changed..
You'll need to rebuild the indexes for that table to have the eventual free space released.Do you have a script of doing that auto for all indexes or should I export each index as script and then delete and recreate?
did you check for duplicated indexes in that tableIndeed there is one duplicate index!
Indeed there is one duplicate index!Actually I will take that back..as I was looking at the properties window of that index and it only showed one column (see attached), while when I looked at the script it shows the following..
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
#2CREATE 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
It's already split
<<Now my question is, how can I shrink the size of the file in order to prolong the need to upgrade (FE/BE)?>>
Do a shrink database on it from SSMS.
<<How can I see what indexes are not being used so frequent and can be dropped (for a particular table or in general)?>>
There are many articles out on the net on how to get index stats. Once such:
https://www.sqlskills.com/blogs/paul/indexes-from-every-angle-how-can-you-tell-if-an-index-is-being-used/
Jim.