Wednesday, August 28, 2013

[MS SQL Server] View Contents of a Log Backup

[MS SQL Server] View Contents of a Log Backup

View Contents of a Log Backup

Posted: 28 Aug 2013 01:33 AM PDT

I noticed in one of my overnight scheduled log backups that it was huge 500MB for 800MB database. Usually my log backups are relatively small 30MB. I would like to view the contents of the file so that I can see what happened.I can't seem to find a way to do this, can you tell me if there is a way to inspect the backup file to see what transactions occurred?Thanks,Tom

SSIS - Need Recommendations

Posted: 28 Aug 2013 04:12 AM PDT

I need to copy a number of tables from an ERP system's Progress database to a SQL Server database that will be used for reporting. I have a working package, but was wanting recommendations on how to improve the performance.The Progress database has tables grouped by function, for example AR (Accounts Receivable) and AP (accounts Payable). I have strung a number of Sequence Containers together, each containing the transfer steps for each grouped function. They are linked together and are set to continue on success. Some tables will be completely replaced. Other large tables that contain data that can be grouped by year, like order history, are usually partially replaced, but may be fully replaced. For example, I will normally delete the last 2 years of order data and replace it. However, once a month I will do a complete wipe and refresh.Each Sequence Container contains the following steps:1. Drop Indexes - An Execute SQL task that drops any non-primary indexes from the tables getting transferred2. Truncate Tables - An Execute SQL task that truncates any tables that are being fully replaced3. Delete Tables - An Execute SQL task that deletes data from tables that will be partially loaded, like the order data.4. A series of Data Flow tasks for each table. Each task does an ADO Net Source to an ADO Net Destination. The source being a SQL query that pulls only the columns I need. The destination being a SQL Server table. For large tables, I will use a For Loop Container to load data in chunks, for example, order data by year.5. Recreate Indexes - An Execute SQL task that creates any non-primary indexes for the tables getting transferredNothing overly complicated here. I do have a couple of tables that are driven off of other tables, like the order detail table. That mainly affects the way the ADO Net Source query is generated. The Truncate/Delete tasks may also be enabled/disabled based on whether it is the once a month run or the daily run.Like I said, this works fine. It just takes over an hour and a half to run. I did try doing a couple of tests with one of the tables. The first test was to write the source results to a flat file and then using Bulk Insert task. I also tried changing the ADO Net Destination to a SQL Server Destination. This one required me to add a data conversion transformation step due to the way the Progress ODBC returns string and date data. Both attempts took near or slightly longer than my original method.Should I break all this up into separate packages? Should I try to flow these so there are multiple tables being concurrently loaded? Anything else I should be looking at?Thanks.

SQL Server License Structure

Posted: 27 Aug 2013 11:36 PM PDT

I would like to know the sql server license structure in our company. We don't have any documentation on it. I need to find out how the license structure is distributed across all servers whether is CPU based or CAL based or any other. It might be different for different servers. What's the best to find out??

Services are stopped automatically

Posted: 27 Aug 2013 09:09 PM PDT

Hi All,we have one server which is have windows 2008r2 and sql server2008r2. here we have three instances but in that 2 named instances for named instances services are stopped automatically.we checked in error log the message is:2013-08-28 01:47:51 - ? [100] Microsoft SQLServerAgent version 10.50.1600.1 ((Unknown) unicode retail build) : Process ID 21562013-08-28 01:47:51 - ? [101] SQL Server ARSAMANT-WS05\ION_INST_ETL_02 version 10.50.1600 (0 connection limit)2013-08-28 01:47:51 - ? [102] SQL Server ODBC driver version 10.50.17202013-08-28 01:47:51 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is ARSAMANT-WS05\ION_INST_ETL_022013-08-28 01:47:51 - ? [310] 24 processor(s) and 98243 MB RAM detected2013-08-28 01:47:51 - ? [339] Local computer is ARSAMANT-WS05 running Windows NT 6.1 (7600) 2013-08-28 01:47:51 - ? [432] There are 12 subsystems in the subsystems cache2013-08-28 01:47:52 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent2013-08-28 01:47:52 - ? [129] SQLAgent$ION_INST_ETL_02 starting under Windows NT service control2013-08-28 01:47:52 - + [260] Unable to start mail session (reason: No mail profile defined)2013-08-28 01:47:52 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect2013-08-28 02:16:21 - ! [012] The MSSQL$ION_INST_ETL_02 service terminated unexpectedly2013-08-28 02:16:21 - + [139] AutoRestart: Attempting to restart the MSSQL$ION_INST_ETL_02 service (attempt #1)...2013-08-28 02:16:31 - ! [368] AutoRestart: Unable to restart the MSSQL$ION_INST_ETL_02 service (reason: Access is denied)2013-08-28 02:16:36 - ! [359] The local host server is not running2013-08-28 02:16:36 - + [139] AutoRestart: Attempting to restart the MSSQL$ION_INST_ETL_02 service (attempt #2)...2013-08-28 02:16:36 - ! [368] AutoRestart: Unable to restart the MSSQL$ION_INST_ETL_02 service (reason: Access is denied)2013-08-28 02:16:41 - + [139] AutoRestart: Attempting to restart the MSSQL$ION_INST_ETL_02 service (attempt #3)...2013-08-28 02:16:41 - ! [368] AutoRestart: Unable to restart the MSSQL$ION_INST_ETL_02 service (reason: Access is denied)2013-08-28 02:16:41 - ! [140] AutoRestart: The MSSQL$ION_INST_ETL_02 service could not be restarted after 3 attempts2013-08-28 02:16:41 - + [360] SQLServerAgent initiating shutdown following MSSQL$ION_INST_ETL_02 shutdown2013-08-28 02:16:43 - ! [359] The local host server is not running2013-08-28 02:16:43 - ! [359] The local host server is not running2013-08-28 02:16:43 - ? [098] SQLServerAgent terminated (normally) here default instance is running successfullywhat may be the problem

Regarding monitoring backup failures

Posted: 27 Aug 2013 11:05 AM PDT

Hi all,I am interested in hearing your experience in regards to setting up monitoring for backup failures with Tivoli or SCOM.We currently have just alerts set for backup failures.....what we want to accomplish is, whenever there is a backup failure, it(Tivoli/SCOM) should create a ticket using our ticketing system. Is this something which can be accomplished by Tivoli or SCOM. Have anyone done this before. Please share your experience/thoughts.Thanks,
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog