|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How do I shrink or drop a transaction log and still be able to do backup and recoveryOn my development machine: database_size = 17.4 Mb unallocated_space = 1.9 Mb data + index_size tables = 16.2 Mb So the log files are 1.2 Mb On my production machine: database_size = 18.9 Mb unallocated_space = 2.4 Mb data + index_size tables = 4.3 Mb So the log files are 14.6 Mb This is a big differences, does this come that my provider has reserved so much empty space for the log files? How do I shrink the database in a way that I can still do backup and recovery. SHRINKFILE … TRUNCATEONLY or EMPTYFILE and then drop it with ALTER DATABASE? How do I get a listing of the names (+sizes) of all the log files including transaction logs. Kind regards, Johan van der Galiën. Hello Johan,
Deleting a Transaction Log is not the way to follow to shrink it. It seems that your database's Recovery Model is FULL (or Bulk-Logged?) In that case, you need to backup your log file (using BACKUP LOG command) to empty it. As an initial size, give it some room (say 1-2 GB) so that it won't need to grow physically all the time and cause fragments. In this case, you'll be able to backup your log file and keep its size under control. If you set its recovery model to SIMPLE then you don't need to take log backups to keep its size low as it'll be emptied automatically so it'll not need to grow (except for bulk operations) however in this case you'll not be able to backup your log file so you can return to the lastest point when you take a differential or full backup of your database. See this link: http://technet.microsoft.com/en-us/library/ms189275.aspx -- Ekrem Önsoy <gali***@zonnet.nl> wrote in message news:fa0de479-a75e-49dc-84a6-6fbf0dcdf00c@g38g2000yqd.googlegroups.com... Dear Newsgroup Readers,On my development machine: database_size = 17.4 Mb unallocated_space = 1.9 Mb data + index_size tables = 16.2 Mb So the log files are 1.2 Mb On my production machine: database_size = 18.9 Mb unallocated_space = 2.4 Mb data + index_size tables = 4.3 Mb So the log files are 14.6 Mb This is a big differences, does this come that my provider has reserved so much empty space for the log files? How do I shrink the database in a way that I can still do backup and recovery. SHRINKFILE … TRUNCATEONLY or EMPTYFILE and then drop it with ALTER DATABASE? How do I get a listing of the names (+sizes) of all the log files including transaction logs. Kind regards, Johan van der Galiën. gali***@zonnet.nl (gali***@zonnet.nl) writes:
Show quoteHide quote > On my development machine: You don't. 19 MB is an extremely small database, and there is no> > database_size = 17.4 Mb > unallocated_space = 1.9 Mb > data + index_size tables = 16.2 Mb > > So the log files are 1.2 Mb > > On my production machine: > > database_size = 18.9 Mb > unallocated_space = 2.4 Mb > data + index_size tables = 4.3 Mb > > So the log files are 14.6 Mb > > This is a big differences, does this come that my provider has > reserved so much empty space for the log files? > > How do I shrink the database in a way that I can still do backup and > recovery. reason to shrink it. > How do I get a listing of the names (+sizes) of all the log files sp_helpdb <yourdb>.> including transaction logs. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Dear Sir,
On 26 feb, 00:27, Erland Sommarskog <esq***@sommarskog.se> wrote: > You don't. 19 MB is an extremely small database, and there is no OUTPUT:> reason to shrink it. > > > How do I get a listing of the names (+sizes) of all the log files > > including transaction logs. > > sp_helpdb <yourdb>. name db_size owner dbid created status compatibility_level db_satoconor 18.94 MB sa 61 Sep 26 2008 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 90 name fileid filename filegroup size maxsize growth usage db_satoconor 1 G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA \db_satoconor.mdf PRIMARY 18624 KB Unlimited 1024 KB data only db_satoconor_log 2 G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA \db_satoconor_log.LDF 768 KB 2147483648 KB 10% log only Can I shrink, the 19 Mb DB to 5 Mb that is required for the data, this way: DBCC SHRINKFILE DB_SATOCONOR.MDF TRUNCATEONLY, without losing data and still be able to do backup and recovery? This is important because I only got 100 Mb webspace, including the DB, at my hosting provider so every MB counts! Kind regards, Johan van der Galien. Shrink does not affect your backup options and will not cause data
loss. But don't specify the TRUNCATEONLY option. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi <gali***@zonnet.nl> wrote in message news:3ad956bd-f235-47c7-a506-66c1f38458cf@v38g2000yqb.googlegroups.com... > Dear Sir, > > On 26 feb, 00:27, Erland Sommarskog <esq***@sommarskog.se> wrote: >> You don't. 19 MB is an extremely small database, and there is no >> reason to shrink it. >> >> > How do I get a listing of the names (+sizes) of all the log files >> > including transaction logs. >> >> sp_helpdb <yourdb>. > > OUTPUT: > name db_size owner dbid created status compatibility_level > db_satoconor 18.94 MB sa 61 Sep 26 2008 Status=ONLINE, > Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, > Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, > SQLSortOrder=52, > IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 90 > > name fileid filename filegroup size maxsize growth usage > db_satoconor 1 G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA > \db_satoconor.mdf PRIMARY 18624 KB Unlimited 1024 KB data only > db_satoconor_log 2 G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA > \db_satoconor_log.LDF 768 KB 2147483648 KB 10% log only > > Can I shrink, the 19 Mb DB to 5 Mb that is required for the data, > this > way: DBCC SHRINKFILE DB_SATOCONOR.MDF TRUNCATEONLY, without losing > data and still be able to do backup and recovery? > > This is important because I only got 100 Mb webspace, including the > DB, at my hosting provider so every MB counts! > > Kind regards, > > Johan van der Galien. Dear Sir,
On 28 feb, 09:04, "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote: > Shrink does not affect your backup options and will not cause data I tried: DBCC SHRINKFILE(db_satoconor,6) and it still stays at around> loss. But don't specify the TRUNCATEONLY option. 19 Mb! I am curious why a mdf file that contains only about 4 Mb data stays so recalcitrant at around 18 Mb? There is somehow a lot of empty space reserved in the file? How can I get rid of the empty space? And get my DB back to around 5 MB as it should be?! Kind regards, Johan van der Galien. Show quoteHide quote > > -- > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi > > <gali***@zonnet.nl> wrote in message > > news:3ad956bd-f235-47c7-a506-66c1f38458cf@v38g2000yqb.googlegroups.com... > > > > > Dear Sir, > > > On 26 feb, 00:27, Erland Sommarskog <esq***@sommarskog.se> wrote: > >> You don't. 19 MB is an extremely small database, and there is no > >> reason to shrink it. > > >> > How do I get a listing of the names (+sizes) of all the log files > >> > including transaction logs. > > >> sp_helpdb <yourdb>. > > > OUTPUT: > > name db_size owner dbid created status compatibility_level > > db_satoconor 18.94 MB sa 61 Sep 26 2008 Status=ONLINE, > > Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, > > Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, > > SQLSortOrder=52, > > IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 90 > > > name fileid filename filegroup size maxsize growth usage > > db_satoconor 1 G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA > > \db_satoconor.mdf PRIMARY 18624 KB Unlimited 1024 KB data only > > db_satoconor_log 2 G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA > > \db_satoconor_log.LDF 768 KB 2147483648 KB 10% log only > > > Can I shrink, the 19 Mb DB to 5 Mb that is required for the data, > > this > > way: DBCC SHRINKFILE DB_SATOCONOR.MDF TRUNCATEONLY, without losing > > data and still be able to do backup and recovery? > > > This is important because I only got 100 Mb webspace, including the > > DB, at my hosting provider so every MB counts! > > > Kind regards, > > >Johan van der Galien.- Tekst uit oorspronkelijk bericht niet weergeven - > > - Tekst uit oorspronkelijk bericht weergeven - Are you doing transaction log backups? If not, the transaction log cannot
release the used log portion because it's still in-use. Either do regular transaction log backups, or put the database into Simple mode where the transaction log is automatically marked as free once a transaction has committed. If you regularly do db maintenance, like shrinking the db, that will cause the transaction log to grow unless the db is in Simple mode. The web hosting service I use does nightly full backups, but no transaction log backups. I want the log available for recovery, so I keep the db in Full mode. When I do major updates like reloading the web database, I put the db into Simple mode and shrink the db. Then I re-enable Full mode. After the next nightly full backup, the transaction log is again usable if necessary. <gali***@zonnet.nl> wrote in message news:dc0f6ce1-7911-466e-be7e-9514b0316fd6@e18g2000yqo.googlegroups.com... On 28 feb, 09:04, "Tibor Karaszi"Dear Sir, <tibor_please.no.email_kara***@hotmail.nomail.com> wrote: > Shrink does not affect your backup options and will not cause data I tried: DBCC SHRINKFILE(db_satoconor,6) and it still stays at around> loss. But don't specify the TRUNCATEONLY option. 19 Mb! I am curious why a mdf file that contains only about 4 Mb data stays so recalcitrant at around 18 Mb? There is somehow a lot of empty space reserved in the file? How can I get rid of the empty space? And get my DB back to around 5 MB as it should be?! Kind regards, Johan van der Galien. Show quoteHide quote > > -- > Tibor Karaszi, SQL Server > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi > > <gali***@zonnet.nl> wrote in message > > news:3ad956bd-f235-47c7-a506-66c1f38458cf@v38g2000yqb.googlegroups.com... > > > > > Dear Sir, > > > On 26 feb, 00:27, Erland Sommarskog <esq***@sommarskog.se> wrote: > >> You don't. 19 MB is an extremely small database, and there is no > >> reason to shrink it. > > >> > How do I get a listing of the names (+sizes) of all the log files > >> > including transaction logs. > > >> sp_helpdb <yourdb>. > > > OUTPUT: > > name db_size owner dbid created status compatibility_level > > db_satoconor 18.94 MB sa 61 Sep 26 2008 Status=ONLINE, > > Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, > > Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, > > SQLSortOrder=52, > > IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 90 > > > name fileid filename filegroup size maxsize growth usage > > db_satoconor 1 G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA > > \db_satoconor.mdf PRIMARY 18624 KB Unlimited 1024 KB data only > > db_satoconor_log 2 G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA > > \db_satoconor_log.LDF 768 KB 2147483648 KB 10% log only > > > Can I shrink, the 19 Mb DB to 5 Mb that is required for the data, > > this > > way: DBCC SHRINKFILE DB_SATOCONOR.MDF TRUNCATEONLY, without losing > > data and still be able to do backup and recovery? > > > This is important because I only got 100 Mb webspace, including the > > DB, at my hosting provider so every MB counts! > > > Kind regards, > > >Johan van der Galien.- Tekst uit oorspronkelijk bericht niet weergeven - > > - Tekst uit oorspronkelijk bericht weergeven - Paul Shapiro (p***@hideme.broadwayData.com) writes:
> Are you doing transaction log backups? If not, the transaction log cannot 1) Johan is trying to shrink is MDF file.> release the used log portion because it's still in-use. 2) Per his previous post, his database is in simple recovery. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx What version of SQL Server? Perhaps space usage reporting is
out-of-whack? Check out DBCC UPDATEUSAGE. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi <gali***@zonnet.nl> wrote in message news:dc0f6ce1-7911-466e-be7e-9514b0316fd6@e18g2000yqo.googlegroups.com... On 28 feb, 09:04, "Tibor Karaszi"Dear Sir, <tibor_please.no.email_kara***@hotmail.nomail.com> wrote: > Shrink does not affect your backup options and will not cause data I tried: DBCC SHRINKFILE(db_satoconor,6) and it still stays at around> loss. But don't specify the TRUNCATEONLY option. 19 Mb! I am curious why a mdf file that contains only about 4 Mb data stays so recalcitrant at around 18 Mb? There is somehow a lot of empty space reserved in the file? How can I get rid of the empty space? And get my DB back to around 5 MB as it should be?! Kind regards, Johan van der Galien. Show quoteHide quote > > -- > Tibor Karaszi, SQL Server > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi > > <gali***@zonnet.nl> wrote in message > > news:3ad956bd-f235-47c7-a506-66c1f38458cf@v38g2000yqb.googlegroups.com... > > > > > Dear Sir, > > > On 26 feb, 00:27, Erland Sommarskog <esq***@sommarskog.se> wrote: > >> You don't. 19 MB is an extremely small database, and there is no > >> reason to shrink it. > > >> > How do I get a listing of the names (+sizes) of all the log > >> > files > >> > including transaction logs. > > >> sp_helpdb <yourdb>. > > > OUTPUT: > > name db_size owner dbid created status compatibility_level > > db_satoconor 18.94 MB sa 61 Sep 26 2008 Status=ONLINE, > > Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, > > Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, > > SQLSortOrder=52, > > IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled > > 90 > > > name fileid filename filegroup size maxsize growth usage > > db_satoconor 1 G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA > > \db_satoconor.mdf PRIMARY 18624 KB Unlimited 1024 KB data only > > db_satoconor_log 2 G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA > > \db_satoconor_log.LDF 768 KB 2147483648 KB 10% log only > > > Can I shrink, the 19 Mb DB to 5 Mb that is required for the data, > > this > > way: DBCC SHRINKFILE DB_SATOCONOR.MDF TRUNCATEONLY, without losing > > data and still be able to do backup and recovery? > > > This is important because I only got 100 Mb webspace, including > > the > > DB, at my hosting provider so every MB counts! > > > Kind regards, > > >Johan van der Galien.- Tekst uit oorspronkelijk bericht niet > >weergeven - > > - Tekst uit oorspronkelijk bericht weergeven - gali***@zonnet.nl (gali***@zonnet.nl) writes:
> I tried: DBCC SHRINKFILE(db_satoconor,6) and it still stays at around So what is the output from sp_spaceused? Or why do you think that the > 19 Mb! > > I am curious why a mdf file that contains only about 4 Mb data stays > so recalcitrant at around 18 Mb? There is somehow a lot of empty space > reserved in the file? > > How can I get rid of the empty space? And get my DB back to around 5 > MB as it should be?! database size is 5MB? Maybe you should find another host. 100 MB is not much to write home about. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  
Other interesting topics
SSMS 2008 Registered Server problem
Why I cannot see log in Management Studio log file viewer? SSIS Problem SQL Server Agent Not Available in SSMS 2005 Object explorer connect to AS via management studio Can I use Management Studio with Query Analyzer on the same machine? SQLIOSim (Virtual Protect Errors) Escape Template Parameters Escape Template Parameter Visual differentiation by environement in SQL Server Management Studio |
|||||||||||||||||||||||
