Archive
Get to Know How to Shrink Transaction Log in SQL Server
Many times the administrators ask about the transaction log size limit and how to shrink it. Even the log records are not managed properly and at last fill up the disk causing no longer changes to the database. The size of transaction log file will increase due to various reasons like long running transactions, lack of log backups and incorrect recovery model configuration. Thus, it is necessary to reduce the size of transaction log in SQL server. Now, in this blog, we are going to discuss a step-by-step procedure to shrink transaction log in SQL Server in an absolute way.
Shrink Transaction Log in SQL Server – Manual Tricks
There are following methods by which you can easily reduce the size of transaction log file. Please have a look:
- Using SQL Management Studio
a)In the Object Explorer, connect to instance of Microsoft SQL Server Database Engine and expand that instance
b) Expand all Databases and right-click on the database that you need to shrink
c) Choose Tasks, Shrink, and then Files. Then, choose the file type and file name.
d) Alternatively, check the Release unused space check box. If you select this option that causes an unused space in file to be released to operating system and shrink files to the last allocated extent. It will reduces the size of a file without moving any important data.
e) As an alternative, choose this check box Reorganize files before releasing the unused space. If this option is selected, reduce file to value must be identified. Now, the default option is cleared. Select this option causes the unused space in file to be released to operating system and attempt to relocate rows to some unallocated pages
f) Optionally, you need to enter the free space percentage in database file after shrinking the database. A permissible value must be between 0 and 99. This feature is only present when you enabled Reorganize files before releasing unused space
g) Alternatively, choose the Empty file by moving data to other files in similar filegroup check box. Select this option that converts all data from specified file to other files within the filegroup. The empty files can be deleted. This option is same as executing DBCC SHRINKFILE command using EMPTYFILE option.
h) Press OK button
- Using Transaction SQL (T-SQL)
If the database is available in SIMPLE recovery model, thus you can run the following statement to reduce the size of log files:
You must have to replace TestDB_log with logical name of a transaction log file you want shrunk and change 1 to number of MB you wish the transaction log file shrunk to.
If the SQL database is available in FULL recovery model you can set it to SIMPLE, perform DBCC SHRINKFILE cmdlet, and go back to FULL if you do not care about losing data in the transaction log.
You can search the logical name of transaction log file by using a given query:
Another option to reduce the log file size with FULL recovery model is to backup the transaction log for database using BACKUP LOG statement and then the main issue SHRINKFILE to shrink the transaction log in SQL Server:
Time to Conclude
Nowadays, most of the people are aware about how to shrink transaction log in SQL server e., Office 365 Backup. Sometimes, there is an unexpected delay, the log truncation will occur after the checkpoint or after a log backup automatically. Therefore, in this blog, we have explained the manual solution that will help to reduce the size of transaction logs. Make sure you follow each step very carefully.
Learn How to Find Who Deleted Records in SQL Server
Query 1: “Hello friends, I am writing for the first time hoping that some of the learned friends can help me sort out the problem. Some of the tables of my SQL database are accessible to other users too. Yesterday, I noticed that certain rows got missing from the database table. How to find who deleted those rows from that table? Please help me with some genuine method.”
Query 2: “I received a query from a database user who wants to know “how to find who deleted records in SQL Server”. Here, I am talking about the Express edition of database. As per my assumption, this data can be found in the LDF file (if it has not been truncated). If I am correct, tell me how to find out this particular information from LDF file.”
If you want to know how to find out who deleted records from SQL Server, you may find these queries familiar. Hundreds of users like these are looking for ways to find out the culprit who deleted SQL data that caused them serious problem. If you are one of them, hold on, as we will tell you the most impressive method to locate who deleted SQL Server records.
How to Find Who Deleted Records in SQL Server
The record of the user who deleted some data from SQL Server can be found in the Transaction Log file. If you want to know, you need to retrieve that information from LDF file of your SQL database. For this, you will require SQL Transaction Log Viewer that can open and read LDF file data easily. Here is how you can read Log file data using this software.
a. Install and start SQL Log Analyzer Tool of your Windows system. Click on Open to add your log file.
b. A dialog box will appear that will showcase two different tabs for Online Database and Offline Database. Here we will show you the procedure of reading Log file form Online database. Select Online DB Options tab and then choose the Server name from the list. Also, select the Authentication mode and Select Database from the list. Click OK when done.
c. The software will scan the LDF file and prompt when it is finished. Click OK.
d. It will display the summary of LDF file including counts of records, inserts, updates, and deletes. Again, click OK.
e. Now, you will see the complete LDF file data on the software screen. Any record can be previewed.
f. Users can sort the LDF file entries based on Transaction, Login Name, Time, table Name, and Transaction Name.
g. Just click on any table entry to select and preview the corresponding log entry of that operation. If you want to know who deleted a record, click on Delete transaction of that record. You will be able to see the Login Name of the person who deleted that data.
h. If you wish to export these data, check the boxes beside the necessary tables and click on Export.
i. Another dialog box will appear where you can apply record type filter, date filter, Export To/As, Database Credentials for Source database, and Destination Database.
j. Click on Export button when all the fields are filled properly.
k. The tool will export the selected data and upon finishing, prompt the users. Click OK.
l. The software will also display a report which you can save as CSV file for reference.
Note: Besides analyzing Log file data, this software can also retrieve deleted data if the database is in simple recovery mode.
Conclusion
SQL database often contains essential information and deletion of such data causes great inconvenience. Hence, we answered the question how to find who deleted records in SQL Server in this post. Using SQL Log Analyzer, users can open LDF file and find out who performed any particular transaction like Insert, Update, and Delete.
Calculating Datetime Based on NT Time
A colleague of mine gave me an interesting challenge today. I am by no means a T-SQL expert, however it was interesting dissecting the problem.
Give the time, 128271382742968750, what does it mean? How to read this?
We can use command line utility called w32tm.exe with following command to get the exact time…
w32tm.exe /ntte 128271382742968750
Return we get
148462 05:57:54.2968750 – 6/24/2007 8:57:54 AM (local time).
Problem SOLVED!
Well not quite, this doesn’t translate well against many gigs of data that my friend wanted to translate. So reading the KB555936, started breaking down the time-stamp above.
- Multiple 128271382742968750 by 100 to get 12827138274296875000; because the time is recorded in number of 100 ns have ticked by since January 1, 1601.
- Next divide 12827138274296875000 by 1,000,000,000 to get number of seconds passed since January 1, 1601. We get 12,827,138,274.2968750.
- We can ignore everything after the decimal, that is number of ms passed (which we don’t care about).
- Unfortunately we cannot use the DATEADD function in SQL Server to calculate the date, as in SQL Server we can go back to only 1/1/1753. So we need to calculate the number of seconds passed from 1/1/1601 to 1/1/1753 and subtract that from that.
- And that is 4,796,668,800 seconds (you can take my word for it, or calculate it using PowerShell script, below).
- So we take the number calculated in step 3 and subtract 4,796,668,800 from it. To get 8,030,469,474 seconds passed since 1/1/1753. Now we can use our ADDDATE!!! Yeeh? Right?
- Umm unfortunately NO. The DATEADD function accepts a integer parameter, and that number is too big so we get row over flow error :(.
- So we have to do some additional math, we take that number and divide by 60, to get number of minutes passed. We get 133,841,157.90.
- Now the .90 is important as we’ll need to calculate the seconds; so don’t forget it. But we can now pass in the above value to get the date.
- SELECT DATEADD(Minute,133,841,157.90,’1753/1/1′); almost done. DATEADD function truncates any decimal value so we do not get the number of seconds passed.
- So now we have to add the number of seconds to the puzzle. We can do that using SELECT DATEADD(Second,.90*60,DATEADD(Minute,133841157.90,’1753/1/1′)).
- Now we have our final answer of 2007-06-24 05:57:54 :).
- Just for heck of it if we wanted ms also, the answer should be
SELECT DATEADD(MILLISECOND,0.296800000*1000000,DATEADD(Second,.90*60,DATEADD(Minute,133841157.90,’1753/1/1′))).
So there you have it, NT time in normal time using T-SQL :). Lots of work, but possible heh.
SQL Server Script to Calculate the NT Time in Readable formatting using T-SQL, combining all 12 steps into single step:
DECLARE @NTTime BIGINT DECLARE @TimeSkip BIGINT DECLARE @BaseTime DATETIME SET @NTTime = 128271382742968750 SET @TimeSkip = 47966688000000000 SET @BaseTime = '1753/1/1 0:00:00.000' SELECT DATEADD(SECOND,((((@NTTime - @TimeSkip)*1.0)/600000000)-ROUND(((@NTTime - @TimeSkip)/600000000),0,1))*60,DATEADD(MINUTE,((@NTTime - @TimeSkip)/600000000),@BaseTime)) AS NormalTime
PowerShell Script to find time passed between 1/1/1601 and 1/1/1753:
[DateTime]$LowDateRange = '1/1/1601' [DateTime]$HighDateRange = '1/1/1753' $HighDateRange.Subtract($LowDateRange)
INSERTED and DELETED Logical Tables
The INSERTED and DELETED logical tables that exist in SQL Server and allow for handling the data when information is inserted, updated and deleted in DML Triggers only:
DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. You cannot directly modify the data in the tables or perform data definition language (DDL) operations on the tables, such as CREATE INDEX. (Books Online, SQL Server 2008).
Below is summary of what special tables get modified with each DML statement.
| DML Statement | INSERTED | DELETED |
| INSERT | X | — |
| UPDATE | X | X |
| DELETE | — | X |
I wanted to know if there anything special happens when I work with single row versus batch. So I test follow cases …
For testing I Created a new table called ‘IDST_Testing’ (IDST = Inserted Deleted Special Table). Below is summary of test cases and records inserted in each of the special tables.
| Test Case | INSERTED | DELETED |
| Single Insert | 1 | 0 |
| Double Insert – Two Statements | 1/per statement | 1/per statement |
| Single Update | 1 | 1 |
| Double Update – Two Statements | 1/per statement | 1/per statement |
| Single Delete | 0 | 1 |
| Double Delete – Two Statements | 1/per statement | 1/per statement |
| Batch Insert – Two Records | 2 | 0 |
| Batch Update – Two Records | 2 | 2 |
| Batch Delete – Two Records | 0 | 2 |
All those results are normal; but what was surprising was when I was doing batch INSERTED and DELETED the records were in reverse order.
For example:
I inserted following two records:
John
Mary
But when I looked at INSERTED table it showed:
Mary
John
In my actual table it was in order what I entered in but when processing the INSERTED and DELETED table they are revered. It was same in tables with IDENTITY columns and without.
Another interesting information on these tables:
- In SQL Server 2000, these logical tables internally refer to database transaction log to provide data when user queries them.
- In SQL Server 2005, these logical tables are maintained in tempdb and they are maintained using the new technology Row versioning.
- Accessing of logical tables is much faster in SQL Server 2005 when compared to SQL Server 2000 as the load is removed from transaction log and moved to tempdb.
- Logical tables are never indexed. So, if you are going to loop through each and every record available in these tables, then consider copying data of logical tables to temporary tables and index them before looping through.
Ref: http://blog.techdreams.org/2007/01/logical-tables-of-sql-server-inserted.html
Ref: http://www.sqlmag.com/Article/ArticleID/93465/sql_server_93465.html
When creating trigger it is important to keep in mind that sometimes a Batch Import, Update or Delete might happen against the table; if you are referring the logical tables INSERTED and DELETED the data will be in reverse order so you don’t want to cause issues when traversing through these tables in triggers.
Note always keep in mind RBAR when designing triggers; its very easy set up RBAR scenario when working with triggers. For more information on that please read, http://www.simple-talk.com/sql/t-sql-programming/rbar–row-by-agonizing-row/.
