Tuesday, August 6, 2013

[SQL 2012] Small transaction logs and/or transations logs with no transactions and need to restore

[SQL 2012] Small transaction logs and/or transations logs with no transactions and need to restore

Small transaction logs and/or transations logs with no transactions and need to restore

Posted: 05 Aug 2013 07:35 AM PDT

We are running SQL Server 2012 SP1 64-Bit EE on Windows Server 2008 R2 SP1. We are taking a full backup on Sunday night, a diff backup Monday through Saturday night, and trans log backups Monday through Friday every 10 minutes from 7 A.M. to 6 P.M. We only have a test database set up for right now. I noticed that the TLog Backup for 7 A.M. is 15,524 KB and the TLog Backups from 7:10 to 6 P.M. are only 99 KB each. I understand the TLog backups from 7:10 A.M. to 6 P.M. are small and the same size because of little or no activity on the database. My question is, if there is no (zero) updates on the database, would there be any transactions in the TLog Backup? If there are no transactions in the TLog Backup is it still required for it to be restored in sequence during a recovery (after the full backup restore and diff backup restore)?Thanks in advance, Kevin

Query on a shared and exclusive lock

Posted: 05 Aug 2013 10:43 PM PDT

I have been doing good some research on locks and isolation levels within sql server. From my understanding an exclusive lock will block a request to a shared lock. So if an update is being carried out on a table and another session attempts to carry out a select 7query on the same table, then it will block the select until the update has been carried out (this is Assuming we use read committed isolation). Is this correct?

Table data in tabular form

Posted: 05 Aug 2013 10:20 PM PDT

CREATE TABLE [dbo].[LeaveEntry]( [LeaveId] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, [LeaveTypeName] [int] NOT NULL, [StartDate] [datetime] NULL, [EndDate] [datetime] NULL, )INSERT INTO LeaveEntry VALUES (1, 'A','OUT','2013-08-29 00:00:00.000','2013-09-29 00:00:00.000'), (2, 'B','LON','2013-08-29 00:00:00.000','2013-09-29 00:00:00.000'), (3, 'C','OUT','2013-08-29 00:00:00.000','2013-09-29 00:00:00.000'); I want to do a report in ssis where I have to send an email i a tabular form for each weeksomething likeWeek 1<table border="1px"><tr><td></td><td>A</td> <td>B</td> <td>C</td> </tr><td>Monday</td><td>OUT</td><td>LON</td><td></td></tr><tr><td>Tuesday</td><td></td><td></td><td>LON</td></tr><tr><td>Wednesday</td><td>OUt</td><td></td><td>LON</td></tr><tr><td>Thurday</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td></td><td>OUT</td><td></td></tr></table> Week 2<table border="1px"><tr><td></td><td>A</td> <td>B</td> <td>C</td> </tr><td>Monday</td><td>OUT</td><td>LON</td><td></td></tr><tr><td>Tuesday</td><td></td><td></td><td>LON</td></tr><tr><td>Wednesday</td><td>OUt</td><td></td><td>LON</td></tr><tr><td>Thurday</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td></td><td>OUT</td><td></td></tr></table>

SSIS Question

Posted: 05 Aug 2013 10:47 PM PDT

In training kit(70-463) book .I see following question.What's you guys think should be the answer?As per me its A and C...As per book writer option B is also correct..2. On which SSIS objects can you set checkpoints to be active? (Choose all that apply.)a. Data flow taskB. Control flow tasksC. Sequence containerD. Sort transformation

Semantic search and filetable excel files

Posted: 06 Aug 2013 12:38 AM PDT

Does anyone know if SEMANTICKEYPHRASETABLE and SEMANTICSIMILARITYTABLE work with Excel and CSV files.I'm working on an ap and need to suggest 'tags' for files that users upload, and this is working fine for doc, rtf, pdf and ppt files etc, but the functions return nothing for csv and xls files.I've checked the documents have actually been indexed (by using a freetext query) and it seems like they are..I seem to remember something about the language used for word-breaking being taken from the document metadata (which isn't there on a csv file!) so might this have something to do with it?Any ideas appreciated.Jeff

SQL Server Express with Tools\Advanced for SQL Agent

Posted: 05 Aug 2013 10:25 PM PDT

Hi, I'd heard that if i installed this new SSMS tool on an instance that had Express already installed on it I'd get SSMS which included the Agent etc. So I installed fine and there is an Agent service which started ok. However there is no SQL Agent under SSMS? Can anyone confirm one way or another if there is any Express option that comes with the SQL Agent. If not why does it install with the SQL Agent service?Thank-you

SQL Detection by Edition/Version

Posted: 05 Aug 2013 06:11 AM PDT

This is rather general, not really limited to 2012. My company has requested I find a way to automatically detect which edition/version is installed. Are there file paths, trace files or registry entries that once found on a host id, would tell me the edition/version installed. Looking for a listing of signatures or tags that might assist me in this detection task. I have the ability to customize a signature with all the parameters/variables required, I am just not sure what the parameters might be. Thank you.:

Manage Log file growth

Posted: 05 Aug 2013 04:59 AM PDT

All,We made the switch to Sql Server '12 over the weekend and our main app database log file is slowly growing, past the point where I feel comfortable. We have run a full backup, a differential last night and transaction logs every 15 minutes. But the log file keeps growing and is not shrinking. We need to have the database in full recovery as it is the primary database for a transactional replication publication and the primary database in a availability group. Replication is only 6 second delay and the availability group is up to the second. Any suggestions?Thanks in advance,DK

Transaction Log corruption and SQLVDI

Posted: 05 Aug 2013 07:30 AM PDT

Hello, I have an interesting issue, the cause of which remains a little elusive. We have recently had multiple failures in our transaction log backups, each time seems to result in a corrupt tlog in a similar sequence of events. We are on SQL Server 2012 Enterprise (recently upgraded), running on top of Windows Server 2008R2 Enterprise and are using Red Gate as our third-party backup solution. Twice now, we have suffered a break in our tlog chain, only to find that the tlog which was the breaking point was 'successfully' created, but with issues that are only logged in the Red Gate error log. The error apparently does not get passed up high enough to be trapped in our SQL error checking embedded in the script which does the backup, therefore the backup job does not fail. The error we are seeing is: [quote]----------------------------- ERRORS AND WARNINGS -----------------------------8/4/2013 12:00:14 PM: 8/4/2013 12:00:14 PM: Verifying files:8/4/2013 12:00:14 PM: K:\Backups\[ServerName]_XXXXX\Tranlogs\FileNameX\LOG_NameX_20130804120000.sqb8/4/2013 12:00:14 PM: 8/4/2013 12:00:14 PM: Thread 0 error: Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)8/4/2013 12:00:14 PM: 8/4/2013 12:00:14 PM: SQL error 3013: SQL error 3013: VERIFY DATABASE is terminating abnormally.8/4/2013 12:00:14 PM: SQL error 3254: SQL error 3254: The volume on device 'SQLBACKUP_0C2E9FDE-8A43-41AC-B7C1-2CBE70956DA2' is empty.8/4/2013 12:00:14 PM: Validation of all backup files failed.----------------------- PROCESSES COMPLETED SUCCESSFULLY --------------------8/4/2013 12:00:00 PM: Backing up XXXXX (transaction log) on InstNameX instance to: 8/4/2013 12:00:00 PM: K:\Backups\InstNameX\Tranlogs\FileNameX\LOG_NameX_20130804120000.sqb8/4/2013 12:00:00 PM: BACKUP LOG [NameX] TO VIRTUAL_DEVICE = 'SQLBACKUP_8798FF6C-E2C5-4FCB-8728-EB49BA1335B0' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (NameX), 8/4/2013 12:00:00 PM', DESCRIPTION = N'Backup on 8/4/2013 12:00:00 PM Server: NameX\NameX Database: NameX', FORMAT8/4/2013 12:00:13 PM: Backup data size : 15.625 MB8/4/2013 12:00:13 PM: Compressed data size: 2.790 MB8/4/2013 12:00:13 PM: Compression rate : 82.15%8/4/2013 12:00:13 PM: Processed 1203 pages for database 'NameX', file 'NameXlog' on file 1.8/4/2013 12:00:13 PM: BACKUP LOG successfully processed 1203 pages in 0.609 seconds (15.420 MB/sec).[/quote]The only pattern we can find is a concurrent SQLVDI error which is appearing in the servers Event Logs, which specifically states: [quote]SQLVDI: Loc=SVDS::Open. Desc=Bad State. ErrorCode=(-1). Process=564. Thread=11324. Server. Instance=NameX. VD=Global\SQLBACKUP_A9FCA3B4-CB68-4C45-985F-DB89C6E60D6A_SQLVDIMemoryName_0. [/quote]When the tlog attempts to restore, SQL Server generates the error: [quote]The volume on device 'SQLBACKUP_59E6E383-98CA-4179-8721-FB8379EB6817' is empty.[/quote] ...which appears to indicate corruption in the tlog because it is NOT empty and is the correct and expected size. We have ruled out corruption during the network copy of the tlog to the log-shipped server, since the error is happening closer to home as the backup is actually occurring. Other than the SQLVDI error above, we have not been able to locate any other errors at the SAN, Server, or OS level. The box this is running on is a monster with 1 TB of RAM that is not remotely over-stressed, so I don't believe any sort of memory pressure is the culprit, outside of a buried config that we haven't found yet. The occurrences of this issue have also not followed a day/time pattern and do not coincide with any obvious maintenance or business processes that might be a source of interference. Has anyone experienced similar issues, particularly related to SQL Server 2012 and SQLVDI errors? Would love to hear any experiences at all that might help shed some light on this odd issue. Thank you!
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog