Our SqlServer 2000 database's transaction log is about 70 gig in size. The
Recovery model is "Full". The database and the Transaction log are backed up
everyday, but the Transaction log back up failed because there is not enough
disk space.
How can I reduce the physical file size of Transcaction Log so that I can
back it up and it does not keep growing ?
Thank you.First, to help ensure no potential for any data loss we need to determine if
the 70gb of space being used by the tran log, is being used by data or if it
is free/white-space being held in the transaction log. There are a couple of
ways to check this, one of which involves highlighting the database in EM,
selecting the TaskPad and looking at the amount of space free/in use within
the Transaction Log device.
If a large amount of space is being consumed by data, then you should
perform a transaction log backup, truncate the log and then immediately
perform a full SQL backup to help preserve data. If you cannot perform the
transaction log backup due to the lack of available disk space, then truncate
the log (ex: backup log <dbname> with truncate_only. Immediately after
performing this step, you must perform a full SQL DB backup in order to
ensure the recoverability of your DB. You should then realize that the
remaining space in your transaction log is now free space that should be
released back to the OS. to perform this, execute a DBCC SHRINKFILE
('<TranLogLogicalName>,<minsize>) where the TranLogLogicalName is the logical
name of the tran log device for that db and the minsize is the least size
that you would like to set the tran log to (in Mbs):
ex:
DBCC SHRINKFILE ('MyDb_log',1)
You should then make sure that the database has ongoing tran log backups
that occur on a repeat frequency throughout the day and a daily/weekly
process.
Regards,
"fortunataw" wrote:
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed up
> everyday, but the Transaction log back up failed because there is not enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>|||Hi,
Since you do not have the enogh hard disk space; I recommend you to start
the backup cycle again after truncating the Logs.
Execute the below command to truncate the log
Backup log <dbname> with Truncate_only
After that see the size and utilization log using
DBcc SQLPERF(logspace)
Once you see the utilization is less then:-
Use dbname
go
Dbcc shrinkfile('logical_ldf_file_name',size_in_mb_to_shrink)
go
Get the logical LDF name using the below command:-
use dbname
go
sp_helpfile
Name stands for logical name.
This will ensure that your ldf is shrinked based on the value given.
After doing the above steps do:-
1. Full database backup
2. Start your trasnaction log backup in regular intervals (every 30 minutes
minimum frequency)
The second step will ensure that you LDF will not grow abnormally.
Thanks
Hari
SQL Server MVP
"fortunataw" <fortunataw@.discussions.microsoft.com> wrote in message
news:86FAF3B0-E7F7-4F16-B719-6264666B7694@.microsoft.com...
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed
> up
> everyday, but the Transaction log back up failed because there is not
> enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>
Showing posts with label recovery. Show all posts
Showing posts with label recovery. Show all posts
Wednesday, March 21, 2012
How to reduce the log file size
Hi There,
1) I have a production database in which log file is growing very
drastically eventhough I used "simple recovery model". I am using SQL
server 2000. In order to get rid of this log I have been detaching the
database, deleting the log file and re-attaching the database back.
While doing this production system will be off-line.
I am wondering, if there is a way to get rid of this log from this file
and keep the log file smaller in size.
2) I also need help if there is a way to write a bulk insert or bulk
update statement without increasing the log file.
Thanks in advance,
*** Sent via Developersdex http://www.developersdex.com ***In reverse order ..
In simple recovery model bulk logged operations are minimally
logged. The transaction log will be truncated at the checkpoint.
To shrink the log file ..
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
--
HTH. Ryan
"sasachi sachi sachi" <sasachi@.getmail.com> wrote in message
news:uy6j7X1IGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi There,
> 1) I have a production database in which log file is growing very
> drastically eventhough I used "simple recovery model". I am using SQL
> server 2000. In order to get rid of this log I have been detaching the
> database, deleting the log file and re-attaching the database back.
> While doing this production system will be off-line.
> I am wondering, if there is a way to get rid of this log from this file
> and keep the log file smaller in size.
>
> 2) I also need help if there is a way to write a bulk insert or bulk
> update statement without increasing the log file.
>
> Thanks in advance,
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Firstly, if you are using simple recovery model, the only way the tlog
will grow so large is if you have have any of the conditions in the
below article:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
Now, if you want the log to be small, the only option is shrinking the
log file:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Answer to second question,the best option will be to make your recovery
model Bulk-Logged:
For more info see in BOL: Bulk-Logged Recovery
1) I have a production database in which log file is growing very
drastically eventhough I used "simple recovery model". I am using SQL
server 2000. In order to get rid of this log I have been detaching the
database, deleting the log file and re-attaching the database back.
While doing this production system will be off-line.
I am wondering, if there is a way to get rid of this log from this file
and keep the log file smaller in size.
2) I also need help if there is a way to write a bulk insert or bulk
update statement without increasing the log file.
Thanks in advance,
*** Sent via Developersdex http://www.developersdex.com ***In reverse order ..
In simple recovery model bulk logged operations are minimally
logged. The transaction log will be truncated at the checkpoint.
To shrink the log file ..
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
--
HTH. Ryan
"sasachi sachi sachi" <sasachi@.getmail.com> wrote in message
news:uy6j7X1IGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi There,
> 1) I have a production database in which log file is growing very
> drastically eventhough I used "simple recovery model". I am using SQL
> server 2000. In order to get rid of this log I have been detaching the
> database, deleting the log file and re-attaching the database back.
> While doing this production system will be off-line.
> I am wondering, if there is a way to get rid of this log from this file
> and keep the log file smaller in size.
>
> 2) I also need help if there is a way to write a bulk insert or bulk
> update statement without increasing the log file.
>
> Thanks in advance,
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Firstly, if you are using simple recovery model, the only way the tlog
will grow so large is if you have have any of the conditions in the
below article:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
Now, if you want the log to be small, the only option is shrinking the
log file:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Answer to second question,the best option will be to make your recovery
model Bulk-Logged:
For more info see in BOL: Bulk-Logged Recovery
How to reduce the log file size
Hi There,
1) I have a production database in which log file is growing very
drastically eventhough I used "simple recovery model". I am using SQL
server 2000. In order to get rid of this log I have been detaching the
database, deleting the log file and re-attaching the database back.
While doing this production system will be off-line.
I am wondering, if there is a way to get rid of this log from this file
and keep the log file smaller in size.
2) I also need help if there is a way to write a bulk insert or bulk
update statement without increasing the log file.
Thanks in advance,
*** Sent via Developersdex http://www.codecomments.com ***
In reverse order ..
In simple recovery model bulk logged operations are minimally
logged. The transaction log will be truncated at the checkpoint.
To shrink the log file ..
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH. Ryan
"sasachi sachi sachi" <sasachi@.getmail.com> wrote in message
news:uy6j7X1IGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi There,
> 1) I have a production database in which log file is growing very
> drastically eventhough I used "simple recovery model". I am using SQL
> server 2000. In order to get rid of this log I have been detaching the
> database, deleting the log file and re-attaching the database back.
> While doing this production system will be off-line.
> I am wondering, if there is a way to get rid of this log from this file
> and keep the log file smaller in size.
>
> 2) I also need help if there is a way to write a bulk insert or bulk
> update statement without increasing the log file.
>
> Thanks in advance,
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Firstly, if you are using simple recovery model, the only way the tlog
will grow so large is if you have have any of the conditions in the
below article:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
Now, if you want the log to be small, the only option is shrinking the
log file:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Answer to second question,the best option will be to make your recovery
model Bulk-Logged:
For more info see in BOL: Bulk-Logged Recovery
1) I have a production database in which log file is growing very
drastically eventhough I used "simple recovery model". I am using SQL
server 2000. In order to get rid of this log I have been detaching the
database, deleting the log file and re-attaching the database back.
While doing this production system will be off-line.
I am wondering, if there is a way to get rid of this log from this file
and keep the log file smaller in size.
2) I also need help if there is a way to write a bulk insert or bulk
update statement without increasing the log file.
Thanks in advance,
*** Sent via Developersdex http://www.codecomments.com ***
In reverse order ..
In simple recovery model bulk logged operations are minimally
logged. The transaction log will be truncated at the checkpoint.
To shrink the log file ..
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH. Ryan
"sasachi sachi sachi" <sasachi@.getmail.com> wrote in message
news:uy6j7X1IGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi There,
> 1) I have a production database in which log file is growing very
> drastically eventhough I used "simple recovery model". I am using SQL
> server 2000. In order to get rid of this log I have been detaching the
> database, deleting the log file and re-attaching the database back.
> While doing this production system will be off-line.
> I am wondering, if there is a way to get rid of this log from this file
> and keep the log file smaller in size.
>
> 2) I also need help if there is a way to write a bulk insert or bulk
> update statement without increasing the log file.
>
> Thanks in advance,
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Firstly, if you are using simple recovery model, the only way the tlog
will grow so large is if you have have any of the conditions in the
below article:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
Now, if you want the log to be small, the only option is shrinking the
log file:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Answer to second question,the best option will be to make your recovery
model Bulk-Logged:
For more info see in BOL: Bulk-Logged Recovery
How to reduce the log file size
Hi There,
1) I have a production database in which log file is growing very
drastically eventhough I used "simple recovery model". I am using SQL
server 2000. In order to get rid of this log I have been detaching the
database, deleting the log file and re-attaching the database back.
While doing this production system will be off-line.
I am wondering, if there is a way to get rid of this log from this file
and keep the log file smaller in size.
2) I also need help if there is a way to write a bulk insert or bulk
update statement without increasing the log file.
Thanks in advance,
*** Sent via Developersdex http://www.codecomments.com ***In reverse order ..
In simple recovery model bulk logged operations are minimally
logged. The transaction log will be truncated at the checkpoint.
To shrink the log file ..
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH. Ryan
"sasachi sachi sachi" <sasachi@.getmail.com> wrote in message
news:uy6j7X1IGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi There,
> 1) I have a production database in which log file is growing very
> drastically eventhough I used "simple recovery model". I am using SQL
> server 2000. In order to get rid of this log I have been detaching the
> database, deleting the log file and re-attaching the database back.
> While doing this production system will be off-line.
> I am wondering, if there is a way to get rid of this log from this file
> and keep the log file smaller in size.
>
> 2) I also need help if there is a way to write a bulk insert or bulk
> update statement without increasing the log file.
>
> Thanks in advance,
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Firstly, if you are using simple recovery model, the only way the tlog
will grow so large is if you have have any of the conditions in the
below article:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
Now, if you want the log to be small, the only option is shrinking the
log file:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Answer to second question,the best option will be to make your recovery
model Bulk-Logged:
For more info see in BOL: Bulk-Logged Recovery
1) I have a production database in which log file is growing very
drastically eventhough I used "simple recovery model". I am using SQL
server 2000. In order to get rid of this log I have been detaching the
database, deleting the log file and re-attaching the database back.
While doing this production system will be off-line.
I am wondering, if there is a way to get rid of this log from this file
and keep the log file smaller in size.
2) I also need help if there is a way to write a bulk insert or bulk
update statement without increasing the log file.
Thanks in advance,
*** Sent via Developersdex http://www.codecomments.com ***In reverse order ..
In simple recovery model bulk logged operations are minimally
logged. The transaction log will be truncated at the checkpoint.
To shrink the log file ..
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH. Ryan
"sasachi sachi sachi" <sasachi@.getmail.com> wrote in message
news:uy6j7X1IGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi There,
> 1) I have a production database in which log file is growing very
> drastically eventhough I used "simple recovery model". I am using SQL
> server 2000. In order to get rid of this log I have been detaching the
> database, deleting the log file and re-attaching the database back.
> While doing this production system will be off-line.
> I am wondering, if there is a way to get rid of this log from this file
> and keep the log file smaller in size.
>
> 2) I also need help if there is a way to write a bulk insert or bulk
> update statement without increasing the log file.
>
> Thanks in advance,
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Firstly, if you are using simple recovery model, the only way the tlog
will grow so large is if you have have any of the conditions in the
below article:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
Now, if you want the log to be small, the only option is shrinking the
log file:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Answer to second question,the best option will be to make your recovery
model Bulk-Logged:
For more info see in BOL: Bulk-Logged Recovery
Monday, March 12, 2012
How to reclaim DB space?
Hi
Change the recovery model to simple (found by right
clicking on the database, then going to the options tab).
Then run the following:
backup log <database_name> with truncate_only
go
dbcc shrinkfile (<logical_log_file>, 100)
Note: to get the <ligical_log_file>, run sp_helpfile.
important: This will not cause you to lose any data, BUT
you will not be able to restore using your tx-log backups
anymore (because you just truncated it!). So, make sure
you do a full SQL backup on this database before giving my
script a go.
You can then change the recovery model back to bulk-logged
or Full if you so require.
Enjoy
JonoI tried this and it works fine. Thanks a lot!
"Jono" <anonymous@.discussions.microsoft.com> wrote in message news:<1d8701c4bd4d$01954df0$a6
01280a@.phx.gbl>...
> Hi
> Change the recovery model to simple (found by right
> clicking on the database, then going to the options tab).
> Then run the following:
> backup log <database_name> with truncate_only
> go
> dbcc shrinkfile (<logical_log_file>, 100)
> Note: to get the <ligical_log_file>, run sp_helpfile.
> important: This will not cause you to lose any data, BUT
> you will not be able to restore using your tx-log backups
> anymore (because you just truncated it!). So, make sure
> you do a full SQL backup on this database before giving my
> script a go.
> You can then change the recovery model back to bulk-logged
> or Full if you so require.
> Enjoy
> Jono
Change the recovery model to simple (found by right
clicking on the database, then going to the options tab).
Then run the following:
backup log <database_name> with truncate_only
go
dbcc shrinkfile (<logical_log_file>, 100)
Note: to get the <ligical_log_file>, run sp_helpfile.
important: This will not cause you to lose any data, BUT
you will not be able to restore using your tx-log backups
anymore (because you just truncated it!). So, make sure
you do a full SQL backup on this database before giving my
script a go.
You can then change the recovery model back to bulk-logged
or Full if you so require.
Enjoy
JonoI tried this and it works fine. Thanks a lot!
"Jono" <anonymous@.discussions.microsoft.com> wrote in message news:<1d8701c4bd4d$01954df0$a6
01280a@.phx.gbl>...
> Hi
> Change the recovery model to simple (found by right
> clicking on the database, then going to the options tab).
> Then run the following:
> backup log <database_name> with truncate_only
> go
> dbcc shrinkfile (<logical_log_file>, 100)
> Note: to get the <ligical_log_file>, run sp_helpfile.
> important: This will not cause you to lose any data, BUT
> you will not be able to restore using your tx-log backups
> anymore (because you just truncated it!). So, make sure
> you do a full SQL backup on this database before giving my
> script a go.
> You can then change the recovery model back to bulk-logged
> or Full if you so require.
> Enjoy
> Jono
How to reclaim DB space?
Hi
Change the recovery model to simple (found by right
clicking on the database, then going to the options tab).
Then run the following:
backup log <database_name> with truncate_only
go
dbcc shrinkfile (<logical_log_file>, 100)
Note: to get the <ligical_log_file>, run sp_helpfile.
important: This will not cause you to lose any data, BUT
you will not be able to restore using your tx-log backups
anymore (because you just truncated it!). So, make sure
you do a full SQL backup on this database before giving my
script a go.
You can then change the recovery model back to bulk-logged
or Full if you so require.
Enjoy
Jono
I tried this and it works fine. Thanks a lot!
"Jono" <anonymous@.discussions.microsoft.com> wrote in message news:<1d8701c4bd4d$01954df0$a601280a@.phx.gbl>...
> Hi
> Change the recovery model to simple (found by right
> clicking on the database, then going to the options tab).
> Then run the following:
> backup log <database_name> with truncate_only
> go
> dbcc shrinkfile (<logical_log_file>, 100)
> Note: to get the <ligical_log_file>, run sp_helpfile.
> important: This will not cause you to lose any data, BUT
> you will not be able to restore using your tx-log backups
> anymore (because you just truncated it!). So, make sure
> you do a full SQL backup on this database before giving my
> script a go.
> You can then change the recovery model back to bulk-logged
> or Full if you so require.
> Enjoy
> Jono
Change the recovery model to simple (found by right
clicking on the database, then going to the options tab).
Then run the following:
backup log <database_name> with truncate_only
go
dbcc shrinkfile (<logical_log_file>, 100)
Note: to get the <ligical_log_file>, run sp_helpfile.
important: This will not cause you to lose any data, BUT
you will not be able to restore using your tx-log backups
anymore (because you just truncated it!). So, make sure
you do a full SQL backup on this database before giving my
script a go.
You can then change the recovery model back to bulk-logged
or Full if you so require.
Enjoy
Jono
I tried this and it works fine. Thanks a lot!
"Jono" <anonymous@.discussions.microsoft.com> wrote in message news:<1d8701c4bd4d$01954df0$a601280a@.phx.gbl>...
> Hi
> Change the recovery model to simple (found by right
> clicking on the database, then going to the options tab).
> Then run the following:
> backup log <database_name> with truncate_only
> go
> dbcc shrinkfile (<logical_log_file>, 100)
> Note: to get the <ligical_log_file>, run sp_helpfile.
> important: This will not cause you to lose any data, BUT
> you will not be able to restore using your tx-log backups
> anymore (because you just truncated it!). So, make sure
> you do a full SQL backup on this database before giving my
> script a go.
> You can then change the recovery model back to bulk-logged
> or Full if you so require.
> Enjoy
> Jono
How to rebuild/rescover the master database in sql 2005
Thanks, Mike
I've tried using the below query and it has rebuilded the master database...........
start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME="InstanceName" REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD="NewStrongPassword"
if you have master db backup you can restore it in single user mode as follows,
go to command prompt and type the below command
NET START MSSQLSERVER /c /m then perform the restoration it will work.............
|||Deepak, did you have a master.mdf to start with? In my test, I have none of the master.mdf and other supporting db files to start with. Then you use the "start /wait setup.exe......" and next the "NET START......." and recover?
Thanks, Mike
|||Well, almost got it to work. Now if fails and says my password does not meet the password policy, password is to short..... I'm using a 15 character password. Shouldn't this work? Thanks, Mike
|||Mike,
Pls refer the link, http://deepakinsql.blogspot.com/2007/08/how-to-rebuild-system-database-in-sql.html
I've tried it and its working for me. I've posted it in my blog.....pls check and let me know if it works 
Thanxx
Deepak
|||Deepak, I'm out of the office this week, I will take a look at this later. Thanks for the update. MikeHow to rebuild/rescover the master database in sql 2005
Thanks, Mike
I've tried using the below query and it has rebuilded the master database...........
start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME="InstanceName" REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD="NewStrongPassword"
if you have master db backup you can restore it in single user mode as follows,
go to command prompt and type the below command
NET START MSSQLSERVER /c /m then perform the restoration it will work.............
|||Deepak, did you have a master.mdf to start with? In my test, I have none of the master.mdf and other supporting db files to start with. Then you use the "start /wait setup.exe......" and next the "NET START......." and recover?
Thanks, Mike
|||Well, almost got it to work. Now if fails and says my password does not meet the password policy, password is to short..... I'm using a 15 character password. Shouldn't this work? Thanks, Mike
|||Mike,
Pls refer the link, http://deepakinsql.blogspot.com/2007/08/how-to-rebuild-system-database-in-sql.html
I've tried it and its working for me. I've posted it in my blog.....pls check and let me know if it works
Thanxx
Deepak
|||Deepak, I'm out of the office this week, I will take a look at this later. Thanks for the update. MikeHow to rebuild/rescover the master database in sql 2005
Thanks, Mike
I've tried using the below query and it has rebuilded the master database...........
start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME="InstanceName" REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD="NewStrongPassword"
if you have master db backup you can restore it in single user mode as follows,
go to command prompt and type the below command
NET START MSSQLSERVER /c /m then perform the restoration it will work.............
|||Deepak, did you have a master.mdf to start with? In my test, I have none of the master.mdf and other supporting db files to start with. Then you use the "start /wait setup.exe......" and next the "NET START......." and recover?
Thanks, Mike
|||Well, almost got it to work. Now if fails and says my password does not meet the password policy, password is to short..... I'm using a 15 character password. Shouldn't this work? Thanks, Mike
|||Mike,
Pls refer the link, http://deepakinsql.blogspot.com/2007/08/how-to-rebuild-system-database-in-sql.html
I've tried it and its working for me. I've posted it in my blog.....pls check and let me know if it works
Thanxx
Deepak
|||Deepak, I'm out of the office this week, I will take a look at this later. Thanks for the update. Mike
Subscribe to:
Posts (Atom)