Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Database maintenance
Hi Experts,
We have SQL Express 2005 and I realized our data file is almost reaching the limit (4GB).
Now my question is, how can I shrink the size of the file in order to prolong the need to upgrade (FE/BE)?
Also I see one table that has about 200 MB used for data and 450 MB for indexes, is that acceptable?
How can I see what indexes are not being used so frequent and can be dropped (for a particular table or in general)?
PS. Our front end app is an Access ADP and then therefore I don't see how the database can be split.
Thanks in advance.
We have SQL Express 2005 and I realized our data file is almost reaching the limit (4GB).
Now my question is, how can I shrink the size of the file in order to prolong the need to upgrade (FE/BE)?
Also I see one table that has about 200 MB used for data and 450 MB for indexes, is that acceptable?
How can I see what indexes are not being used so frequent and can be dropped (for a particular table or in general)?
PS. Our front end app is an Access ADP and then therefore I don't see how the database can be split.
Thanks in advance.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
<<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.
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.
@Jim,
@Eric,
Its set to full, should I change that? whats are the pros/cons?
Thanks,
Ben
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.
@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?
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..
Thanks,
Ben
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
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.
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
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.
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
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
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.
Hi Ben,
I personally would not delete any index which is such heavily used as in your example.
But instead you should inspect all queries which are using this table (and others of course) and inspect the execution plan to see if there is any way to improve the queries to better use existing indexes. So maybe it is possible in the end to delete an index because some query could be changed to use another index which fits better. For example, if you find out that some criteria is not really needed in a query so that you can remove it.
From size perspective you should inspect the tables for table and index size. In SSMS 2008 you can do that simply with the "Object Details Explorer" by clicking on the "tables" folder in the Object Explorer and then view the Object Details Explorer which then lists all tables. There you can add additional columns to the list with "Data Space Used" and "Index Space Used" (also "Row Count" for the number of rows in each table). You can use SSMS 2008 also for SQL Server 2005.
If you look at the specifications of SQL Server 2005 Express you'll see that the 4 GB database limit is only about the data files, not the log files, so changing the recovery mode will not help you.
To quote the article:
But of course you can use more than one database for your application. To again quote the article:
So you should consider to plan to move to a higher version of SQL Server in near future.
ADPs can only be connected to one database. But it's no problem to let the backend work with more than one database because the ADP can for example use a view, stored procedure or UDF which assembles a query over different databases. That's very simple: As you mentioned earlier, you use a general SQL Server user to login into the database. This login must be created in the general security settings of the SQL Server. Create a new database and then assign the permissions that you want to this user for the new database. The user can then work with both databases in the same way as with one. If you want to access resources from the other database, simply add the database name before the schema name. Like:
I would first separate all static tables like lookup tables or tables with very low changes into the separate database. Then maybe all tables which are using a lot of disk space but be changed rarely like a customer address table etc.
The better and easier way of course is to upgrade the SQL Server to a higher model, also maybe consider to use SQL Server 2008 with Access 2010 (which is the latest which supports ADP). There is no big impact in upgrading, only in cost (regarding SQL Server, in case of Access there's only one full version licence needed for each developer, the users can work with the free runtime edition).
Separating the tables into different databases would work but you need to reprogram a lot in frontend and backend. If you sum the costs to do that for your costs as developer may be higher as purchasing a SQL Server licence...
Cheers,
Christian
I personally would not delete any index which is such heavily used as in your example.
But instead you should inspect all queries which are using this table (and others of course) and inspect the execution plan to see if there is any way to improve the queries to better use existing indexes. So maybe it is possible in the end to delete an index because some query could be changed to use another index which fits better. For example, if you find out that some criteria is not really needed in a query so that you can remove it.
From size perspective you should inspect the tables for table and index size. In SSMS 2008 you can do that simply with the "Object Details Explorer" by clicking on the "tables" folder in the Object Explorer and then view the Object Details Explorer which then lists all tables. There you can add additional columns to the list with "Data Space Used" and "Index Space Used" (also "Row Count" for the number of rows in each table). You can use SSMS 2008 also for SQL Server 2005.
If you look at the specifications of SQL Server 2005 Express you'll see that the 4 GB database limit is only about the data files, not the log files, so changing the recovery mode will not help you.
To quote the article:
The 4 GB database size limit applies only to data files and not to log files.
But of course you can use more than one database for your application. To again quote the article:
However, there are no limits to the number of databases that can be attached to the server.Means, you can add as many further databases with each 4GB data size as you want. But keep in mind that the Express version also has a limitation of 1 GB memory so I guess you'll get in trouble if you have too many users with too much data querying at the same time...
So you should consider to plan to move to a higher version of SQL Server in near future.
ADPs can only be connected to one database. But it's no problem to let the backend work with more than one database because the ADP can for example use a view, stored procedure or UDF which assembles a query over different databases. That's very simple: As you mentioned earlier, you use a general SQL Server user to login into the database. This login must be created in the general security settings of the SQL Server. Create a new database and then assign the permissions that you want to this user for the new database. The user can then work with both databases in the same way as with one. If you want to access resources from the other database, simply add the database name before the schema name. Like:
SELECT .... FROM dbo.MyTableFromDB1 INNER JOIN DB2.dbo.MyTableFromDB2 ON...
I would first separate all static tables like lookup tables or tables with very low changes into the separate database. Then maybe all tables which are using a lot of disk space but be changed rarely like a customer address table etc.
The better and easier way of course is to upgrade the SQL Server to a higher model, also maybe consider to use SQL Server 2008 with Access 2010 (which is the latest which supports ADP). There is no big impact in upgrading, only in cost (regarding SQL Server, in case of Access there's only one full version licence needed for each developer, the users can work with the free runtime edition).
Separating the tables into different databases would work but you need to reprogram a lot in frontend and backend. If you sum the costs to do that for your costs as developer may be higher as purchasing a SQL Server licence...
Cheers,
Christian
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.
Your indexes look OK to me (and I specialize in tuning indexes). I'm not saying some touch up couldn't be done, but they're good overall, especially since you appear to have the best clustering index, the most critical thing, hooray!
As to the space issue, I suggest trying my standard work around:
1) Create a new db(s).
2) Copy an existing table(s) to the new db
3) In the original db, use a synonym to point that table(s) in the new db. The exact steps are below. What's really cool is that you can leave the table name exactly the same in db1 but have it point to another db.
For example:
Say you have "table1" now in the main db, "db1". Create a new db, "db2". Copy all of "table1", including all its indexes, to "db2". Back on "db1", rename "table1" and then create a synonym for it instead:
CREATE SYNONYM dbo.table1 FOR db2.dbo.table1;
Now try using "table1" from "db1" just like you always did and verify that it works. You may, or maybe not, have to make some permissions adjustments (on db2), but other than that it should work fine. Once it's working, you can delete the original table1.
If you run into any other problems, let me know.
(Btw, do NOT shrink the db, that's not going to gain you much space and could really hurt your performance, unless you've removed a really significant amount of space. So if you move a lot of tables to other dbs, you could consider a one-time shrink then, to make the free space contiguous, although even then it shouldn't really be necessary.)
As to the space issue, I suggest trying my standard work around:
1) Create a new db(s).
2) Copy an existing table(s) to the new db
3) In the original db, use a synonym to point that table(s) in the new db. The exact steps are below. What's really cool is that you can leave the table name exactly the same in db1 but have it point to another db.
For example:
Say you have "table1" now in the main db, "db1". Create a new db, "db2". Copy all of "table1", including all its indexes, to "db2". Back on "db1", rename "table1" and then create a synonym for it instead:
CREATE SYNONYM dbo.table1 FOR db2.dbo.table1;
Now try using "table1" from "db1" just like you always did and verify that it works. You may, or maybe not, have to make some permissions adjustments (on db2), but other than that it should work fine. Once it's working, you can delete the original table1.
If you run into any other problems, let me know.
(Btw, do NOT shrink the db, that's not going to gain you much space and could really hurt your performance, unless you've removed a really significant amount of space. So if you move a lot of tables to other dbs, you could consider a one-time shrink then, to make the free space contiguous, although even then it shouldn't really be necessary.)
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 :(.
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.
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#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,
Thanks,
Ben
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#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: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;
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.
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
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.
Thanks,
Ben
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
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
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
from the attached, can you list some indexes that can be removed?
Thanks,
Ben
Untitled.png
Hi Ben,
Shrinking the field size must be done for each record, SQL Server creates a new table, copies the data, deletes the old table, restore any triggers, indexes and so on. It can take some time to do that depending on the size of your table. So the first is only a warning (as SQL Server does not check if the length of the longest data fits in your new size) that data above 150 chars could be deleted. You can ignore that always if you've checked that on your own like you did. The second only means that this took to much time and could not be completed in the time of your timeout setting so that it was rolled back. You only need to increase the timeout in your SSMS, like described here.
You can safely disable timeouts in SSMS as it always provides the stop button to interrupt a running query.
Cheers,
Christian
I give them the same name as the original tableYes, that's a possible way to create your own kind of "synonym". I personally prefer to also separate objects by giving them an object type prefix like "tbl" or "view". You would have a name "tblX" which is in reality a view. But that's only a naming convention break, no problem for your database.
Shrinking the field size must be done for each record, SQL Server creates a new table, copies the data, deletes the old table, restore any triggers, indexes and so on. It can take some time to do that depending on the size of your table. So the first is only a warning (as SQL Server does not check if the length of the longest data fits in your new size) that data above 150 chars could be deleted. You can ignore that always if you've checked that on your own like you did. The second only means that this took to much time and could not be completed in the time of your timeout setting so that it was rolled back. You only need to increase the timeout in your SSMS, like described here.
You can safely disable timeouts in SSMS as it always provides the stop button to interrupt a running query.
Cheers,
Christian
Don't use SSMS table design view to change table columns. It's preferable to run the correct command:
Also, did you check for duplicated indexes in that table that has 200 MB used for data and 450 MB for indexes?
ALTER TABLE tablename
ALTER COLUMN columnname VARCHAR(150)
Also, did you check for duplicated indexes in that table that has 200 MB used for data and 450 MB for indexes?
@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,
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
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 tableIndeed 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
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
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
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
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial