Backup and recovery-related log files in SQL Server database have MDF and LDF files present in \\sqlserver_install_dir\MSSQL\Data\
1. Document interpretation
. mdf file
MDF is a SQL Server database file.
. ldf file
LDF is a log file of SQL and cannot be opened directly. LDF Records each operation: SELECT.INSERT.DELETE.UPDATE. Log
2. How to recover a database through MDF and LDF files:
1). SQL statements
The following example attaches two files in pubs to the current server.
1 EXEC sp_attach_db @dbname = N ' pubs ',
2 @filename1 = N ' c:\Program files\microsoft SQL Server\mssql\data\pubs.mdf ',
3 @filename2 = N ' c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf '
2). Graphical interface
All tasks, database, Resource Manager, additional database---Select File--Confirm
If the database is running for a long time and a large number of operations, you will find that the LDF file is unusually large,
3. How do I intercept ms-sql Transaction Log?
These logs grow at a speed that is not as small as you think, and often the actual material does not grow any longer (. MDF) but trading records (_log. LDF) is multiplied by the number of
The following languages are the truncated script for Ms-sql 2000 and Ms-sql 2005:
--1. Cut Transaction Records
BACKUP LOG [Information Library name]
With TRUNCATE_ONLY
--2. Display the archive file and find out the logically name of the transaction records
EXEC sp_helpdb ' Information repository name '
--3. Compression transaction Records
Use Information Library name
DBCC shrinkfile ([Information Library name _log],2)
2 after execution is referred to as 2M
The main function of the above language is in this part of the Shrinkfile, which is the direct reality of the log file is truncated, and also back to the physical disk space
Of course, if you are accustomed to doing transcation log, this action can be done in the last step of the job.
4. How do I shrink a database file?
Shrink the database file (the database file does not decrease if it is not compressed)
Enterprise Manager--right-click the database you want to compress--all tasks--shrink the database--Shrink the file
--Select Log file--select shrink to XXM in the shrink mode, here will give an allowable shrinkage to the minimum m number, directly enter this number, OK.
--Select the data file--choose Shrink to XXM in the contraction mode, here will give a allowable shrinkage to the minimum m number, directly enter this number, OK.
5. How can I automatically shrink after setting?
Enterprise Manager--server--right-click Database-Properties--Options--select "Auto Shrink"
6. How do you want to keep the log from growing too much later?
Enterprise Manager--server--right-click Database-Properties-transaction log-limit file growth to XM (x is the maximum data file size you allow)
SQL Server log file is too large to handle