Database maintenance

<<PS. Our front end app is an Access ADP and then therefore I don't see how the database can be split.>>

 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.

Check if your recovery model is set to Simple.

@Jim,

It's already split

Right, 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.

@Eric,
Its set to full, should I change that? whats are the pros/cons?

Thanks,
Ben

bfuchs,
The number of people who actually know anything about the ADP is rapidly shrinking.  The ADP has been deprecated and I think it doesn't support anything newer than SQL Server 2005 or possibly 2008.   Do you have a plan for migration?

@Jim,
Trying the following as mentioned there and all result columns are null..

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 allOpen in new window

@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.

Thanks,
Ben

>>Its set to full, should I change that? whats are the pros/cons?

And you are probably not doing backup with truncate log! That means that your transaction is what is filling all your 4GB! A full recovery database, if you have the correct backups, can be restored to a more recent point. If you don't have a big transactional database, chances are that simple recovery database (where the log is committed every time a transaction ends) is good enough for you.

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

 

Select allOpen in new window

Thanks,
Ben

>>and got to 3.75 GB, so that means its full of data, no?

in that case yes. or full of spaces! ie, if a table has a field declared CHAR(500), it will always have 500 characters worth of data even if only really contain a few characters. Also, if your fields are declared as NCHAR or NVARCHAR or N*, and you don't use any characters other then regular letters , your fields is twice as big as it should be.

OK, lets summarize what can be done momentarily to alleviate the burning issue..

1- regarding those mentioned, I always declare them as varchar(n), so only the first point might be relevant regarding the field size, however for that will have to go over table by table, field by field to inspect that, unless you know of a general routine that I can get all fields that size is lets say double of the longest text entered..

2- Regarding removing unused indexes, I checked the largest table and saw the attached, would you be able to tell which can for sure be deleted?

Thanks,
Ben
Untitled.png

Hi Experts,

Meanwhile you can also take a look at the following..
https://www.experts-exchange.com/questions/29013755/How-do-I-figure-out-how-much-database-is-expected-to-grow.html

Thanks,
Ben

I would delete the last three indexes and then shrink.  If the users complain about slowness, put them back, one at a time until you figure out if any can be deleted.  The nice thing about deleting the indexes is you can easily put them back.

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.
By the way, the limit is only for data files (.mdf) and not the transaction log files (.trn).

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.

Yes.  IF you have that situation in your Express dbs (somewhat unusual), then, if possible, you can still:

Move all RI-"paired" tables to the same separate db.  That is, if tbl2 has fk to tbl1, and tbl3 has fk to tbl1, then move tbls 1, 2 and 3 to the same external db.  Tbls4+ could still be in the original db or other external dbs.

If you have so much RI  you can't disentangle the tables at all (wow!), then, yeah, you'll still have a size problem :(.

I would hazard a guess that I have very few applications where I could "disentangle" the RI to separate the tables into multiple BE's.  Maybe my apps are more cohesive.  I do have occasional tables that are not related to anything but they are oddballs and far from the norm.

It's actually very easy to write code to "trace" RI dependencies.  I've never seen an Express system that entangled.  Most tables just don't have that many natural relationships anyway.

At any rate, all that's irrelevant anyway unless the OP both actually has RI and it's significant enough to be an issue for him/her.

Hi Experts,

Sorry for the long delay in response, we just finished discussing all thats have been suggested here..

First would like to thank all for your valued input.

Just before taking any steps in splitting db's would like to know the following.

According to the following thread https://www.experts-exchange.com/questions/29013755/How-do-I-figure-out-how-much-database-is-expected-to-grow.html?anchor=a42078570¬ificationFollowed=186921251&anchorAnswerId=42078570#a42078570
we should have at least another 2-3 months to go, do you all agree with this?
Most likely by then we will have our DB upgraded to 2008 which as few mentioned, have more then doubled the limit..

@Bit, @Scott,

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

Comparing this approach to what Bitsqueezer suggested (the option of using a view) what are the pros/cons?

Thanks,
Ben

With Bitsqueezer's method, you have to hard-code the table location in actual queries.  If you decide to move the table back into the main db -- such as when the size increases to 10GB -- you'd have to change the code yet again.

WIth the synonym method, you do not -- in fact, you do not need to change any changes to existing code.

Hi Ben,

in both cases you would first move some tables to a separate database on the same server. 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).

I'm not sure if Access can handle the access to synonymed tables as in the background it uses some MS stored procedures to create i.e. UPDATE/INSERT/DELETE dynamic SQL and also it reads the metadata of the tables to get information about them, like field sizes, datatypes etc. Synonyms hiding these metadata to any other database using them so even SSMS is not able to tell you anything about a synonym object (the object behind the synonym of the other database) so maybe Access gets in trouble accessing these tables.
This should be no problem with a database name prefix as you directly access the desired object.
But in fact, I've never tried that, I use synonyms only in my current project to access SPs etc. but not base objects like tables. So I cannot say if Access can handle that but you can simply try that on your own: Create an empty database, create a table, create a synonym for that table in your current database and then try to create a form based on this table. I guess you will not be able to automatically create a form based on the synonym table name because of the metadata, but you can create the form based on the second databases table, then switch the connection back to the first database and try to use the synonym name as RecordSource for the form (metadata for the form itself like field names etc. would be already in the form on this way). Try to change data in this form then, if Access is able to use the synonym table and create the right SQL statements to change the data. Could be tricky. Don't know what will happen then.

I also agree to Pat, I guess you will not be able to create referential integrity over two databases, never tried that. But you could of course use trigger code to make sure that a foreign key exists, but would need a lot of careful programming to do that on your own.

Again I would much more recommend to use a higher version of SQL Server (also for performance reason, in the simplest Express edition 2008R2 has far better performance than SQL Server 2005). I would not go on using an Express version with the expected amount of data growth.
By the way you could also consider to move old data to a data warehouse database which would also shrink the amount of data. Normally you do not use data which is older than a few years, only for search and archive purposes.

Cheers,

Christian

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..
will try now the other option with view & let you know.

Thanks,
Ben

Just tried the view approach and this seems to work fine with access.

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..

Thanks,
Ben

btw, looking at the attached would you see something unusual that's prompting for attention?

Thanks,
Ben
Untitled.png

Meanwhile I went on following Erics suggestion (all the way on top..) re reducing field sizes, and realized that one of the largest tables had a field varchar(250) while the max(len(Field))=147, so I tried changing the size to varchar(150) and got the errors attached, while I'm pretty sure nobody has this table open at this time, therefore wondering whats causing this error?

Thanks,
Ben
Untitled.png
Untitled1.png

Just to add one more..
from the attached, can you list some indexes that can be removed?

Thanks,
Ben
Untitled.png

@bit,
I will probably start with small stuff like reducing field sizes and deleting unused indexes, as relocating tables would need some testing of users.. in addition if we end up upgrading to 2008 soon that will not be necessary, while those small touch ups are always helpful.

@Vitor,

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 table

Indeed there is one duplicate index!
Now does it makes a diff which of them gets deleted, do I have to look for usage etc?
Also when deleting an index, does it get immediately additional space or do I also have to rebuild all indexes of that table?

Thanks,
Ben

Can someone tell me what the maximum SQL Server version is that is supported by the A2010 ADP.  Based on the date of the last version that fully supports ADP, it could be 2008 but based on when the ADP went the way of the Do-Do, it could be 2005.

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..
#1

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 allOpen in new window

#2

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 allOpen in new window

Thanks,
Ben
Untitled.png

Hello Pat,

regularly it is SQL Server 2008 R2 as this is the newest version which was available when A2010 came out which is the last version supporting ADPs. I work with this combo for the last years.

It could be possible to work with a newer SQL Server version but there is no guarantee that these have the needed SPs Access needs to work with SQL Server with ADPs. Of course it will not support the newer datatypes introduced after 2008R2.

Cheers,

Christian

I got an error while trying to rebuild the PK index, see attached.
Perhaps I don't need to rebuild the PK index in order to regain the free space?

Thanks,
Ben
Untitled.png
Untitled1.png

Oh My Experts,

I Ended up with doubling the size of my table!!
All I did was changed the size from varchar(250) to 150 and rebuild two indexes..

What should I do?!

Thanks,
Ben
Untitled.png

Thanks Christian.

I ended up calling a DBA consultant to bail me out of this..
However I do appreciate very much all your efforts & advice!