Tuesday, April 9, 2013

[SQL Server 2008 issues] When to update statistics manually, if at all?

[SQL Server 2008 issues] When to update statistics manually, if at all?

When to update statistics manually, if at all?

Posted: 08 Apr 2013 06:59 AM PDT

So, I'm using my google foo to try to find some articles about why we should not be updating statistics and recompiling on a daily basis. It is pretty clear that with auto_update_statistics enabled, there is no reason whatsoever to recompile after the statistics update. However, I cannot find any information about how to tell when we should update the statistics. I would like to believe that we shouldn't have to at all, but I have to convince myself of that as well. Instead of just saying "well, statistics are created and updated manually, so we don't need to update them daily." I need some references. Google is only showing me how to do it and what it does... not how to determine when I should do it. Any help appreciated.

Sum 'LastPeriod' short version

Posted: 08 Apr 2013 01:26 PM PDT

Maybe my last post was too long ...Does anyone know if the results of a 'LastPeriod' MDX query can be added together & if so, how?Thanks

Write query for Suond like search

Posted: 08 Apr 2013 06:33 PM PDT

hello everyoneI am working on database where I would require to make search which base on sound like search:e.g.In my search values 'Poonam' and 'Punam', 'Veenita' and 'Vinita' both are same but 'Ramita' and 'Amita', 'Amita' and 'Ankita' ,Both are different.can any one suggest me how to write such query.

Add date range by record line

Posted: 08 Apr 2013 12:37 PM PDT

I'm trying to add a date range to this query, where the min date is the last 10 days, the max TheDate. The Min/Max are calculated for records that fall within thoe dates. I tried all kind of combinations and came up with nothing. Hope someone can solve this puzzle, Thanks![code="sql"]CREATE TABLE #Sample (TheDate datetime, Client varchar(10), Calls int, Answers int, Sales int) INSERT INTO #Sample SELECT '2013-04-03', 'A', 50, 20, 60 UNION ALL SELECT '2013-04-04', 'A', 60, 30, 50 UNION ALL SELECT '2013-04-05', 'A', 70, 40, 40 UNION ALL SELECT '2013-04-06', 'A', 80, 50, 30 UNION ALL SELECT '2013-04-07', 'A', 90, 60, 20 UNION ALL SELECT '2013-04-08', 'A', 100, 70, 10 UNION ALL SELECT '2013-04-03', 'B', 150, 120, 160 UNION ALL SELECT '2013-04-04', 'B', 160, 130, 150 UNION ALL SELECT '2013-04-05', 'B', 170, 140, 140 UNION ALL SELECT '2013-04-06', 'B', 180, 150, 130 UNION ALL SELECT '2013-04-07', 'B', 190, 160, 120 UNION ALL SELECT '2013-04-08', 'B', 200, 170, 110 SELECT Client, TheDate, COUNT(1) as Records, --number of days with records MAX(Calls) as MaxCalls, MIN(Calls) as MinCalls, MAX(Answers) as MaxAnswers, MIN(Answers) as MinAnswers, MAX(Sales) as MaxSales, MIN(Sales) as MinSales FROM #Sample WHERE TheDate BETWEEN DATEADD(D, -10, TheDate) AND TheDate GROUP BY Client, TheDate ORDER BY Client, TheDate[/code][code="plain"]Result: Client TheDate Records MaxCalls MinCalls MaxAnswe MinAnswe MaxSales MinSalesA 4/3/2013 1 50 50 20 20 60 60A 4/4/2013 2 60 50 30 20 60 50A 4/5/2013 3 70 50 40 20 60 40A 4/6/2013 4 80 50 50 50 60 30A 4/7/2013 5 90 50 60 60 60 20A 4/8/2013 6 100 100 70 70 60 10B 4/3/2013 1 150 150 120 120 160 160B 4/4/2013 2 160 150 130 120 160 150B 4/5/2013 3 170 150 140 120 160 140B 4/6/2013 4 180 150 150 120 160 130B 4/7/2013 5 190 150 160 120 160 120B 4/8/2013 6 200 150 170 120 160 110[/code]

Transaction Log Growth

Posted: 08 Apr 2013 07:35 AM PDT

How to find the transaction/process/Job which cause growth of transaction log size?Thanks

CASE statement

Posted: 08 Apr 2013 12:28 PM PDT

[code="sql"] WHERE SS.[Key]='A' AND SO.[type]='X_P' AND vu.[Status]=1 AND 1= CASE WHEN @CreateDateDatTime > vu.LastRunDate THEN 1 ELSE ---Secondary filter ( L.create_date>=ISNULL(vu.LastRunDate,'01/01/1900') OR L.modify_date>=ISNULL(vu.LastRunDate,'01/01/1900') )[/code]What i am trying to do here is to use the case in the filter, if @CreateDateDatTime>vu.LastRunDate THEN don't apply the secondary filter (select everything that satisfies other filters) or else apply the secondary filter as well. Can't put it together. any help?

Trace flag 8295?

Posted: 08 Apr 2013 06:16 PM PDT

Hi,Does anyone have where trace flag 8295 is used for. I found it active on a customer server, SQL Server 2008 R2 SP2 Enterprise Edition, but I can't figure out the purpose of it. When I search the internet, nobody seems to know this trace flag.Can anyone help me?RegardsPieter

Parallel Data Warehouse for OLTP

Posted: 08 Apr 2013 05:10 PM PDT

Hi,Is Parallel Data Warehouse feature of SQL Server 2008 R2 targeted only for OLAP/BI impementations?Can we use it for hosting OLTP databases as well?Thanks.

Can I SUM the 'LastPeriod' query results?

Posted: 08 Apr 2013 12:57 PM PDT

Hi,I want to create 'Rolling' Month on Month, Quarter on Quarter queries with Revenue data.So, for example if I want to create a Quarterly Reveneue variance (which I would calculate as the Last months Revenue minus the First Months Revenue & divide that revenue amount in to the Last months Revenue amount)My Quarterly query therefore needs to include the 'End Month' let's say June 2012 & contain the 'Billed_Amount' field to find the Revenue for a given month.I thought I'd use the 'Lastperiod' function to collect 3 months back from June 2012 & SUM those amounts to use in a variance calculation with the next quarter & so on.Here's my MDX query that I'm checking in SSMS to ensure it's correct before I add it to the Calculations in BIDS;SELECT LastPeriods(3,[MTH_QTR_HLF_YR].[Month].&[201206]) ON 0FROM [RM RADAR ACCT CUBE]This gives me the months I need & counts the records, OK so far...Then I add the 'Total_Billed' Measure to get the Revenue figures;SELECT LastPeriods(3,[MTH_QTR_HLF_YR].[Month].&[201206].[Measures].[Total Billed]) ON 0FROM [RM RADAR ACCT CUBE]I don't get anu results for this query, not sure why & I want to SUM these 3 'Total_Billed' amounts once this query works.So, I'm thinking I could then use this to SUM the 'Total_Billed';SELECT SUM(LastPeriods(3,[MTH_QTR_HLF_YR].[Month].&[201206].[Measures].[Total Billed])) ON 0FROM [RM RADAR ACCT CUBE]Can anyone suggest how I best use 'Lastperiod' OR should I use another Function altogether??Thanks

Are Queries Actually Using the Index

Posted: 08 Apr 2013 12:19 PM PDT

Hello EveryoneI have a SQL database that is the backend to a Java application. There are indexes on the column in the tables. The java app does not use any stored procedures. It passes in all the queries from the front-end. Some are simple and some are dynamically built on the front-end.How can I see if the query that is being passed in, is actually using the index or performing a full table scan?It seems that some of the queries are taking a long time to return any data to the front-end. I just need to verify what is actually happening.Thank you in advance for all your assistance, comments and suggestions.Andrew SQLDBA

SSIS script task

Posted: 08 Apr 2013 06:50 AM PDT

I need to set the unicode property of a connection manager (which is a variable) to true in a Script Task. Can you please help?

Active Directory Connection

Posted: 08 Apr 2013 11:26 AM PDT

At work we have an application that has the ability to use 3 active directory groups to setup security within the application (Admin, User, Read Only). The application access a SQL Database (typically 2008 R2), and I am being told that I don't need to have these AD groups setup with the SQL environment (such as new login) for this authentication to work. However, we have been having problems with this setup for some time now, but the engineers who created this insist this works.We do use an AD settings file to direct it to the AD server and location, however, the specific groups are not a part of this file. The group names are stored within the SQL Database, so a successful authentication to the Database should be needed to obtain this information. Thus, I am being told, that once everything is setup, I should be able to connect in using a Windows Authentication, and our software will be able to accesses several databases within a single instance without any user information being listed in Management Studio. When I have tried to do this with the last few customers I have worked with, unless we setup the AD groups themselves within the SQL environment, we have not been able to get this to work.Any suggestions on what I could be doing wrong? Any assistance on how this could be working, or if it should be working at all would be greatly appreciated.ThanksLD

New Linked Server Uses Local Catalogs - Not Those on the Remote Server

Posted: 08 Apr 2013 09:07 AM PDT

I have tried every way I can think of to define a linked server so that I can query catalogs on a remote production server. Problem is, when I expand the catalogs on the new linked server, it shows the catalogs on the local server where it's defined - and not the catalogs on the remote server! Could it be that the login on the remote server is invalid - so it defaults to the catalogs on the local server? Here's the code:EXEC master.dbo.sp_addlinkedserver @server = N'ProdServer', @srvproduct=N'SQL Server'EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ProdServer',@useself=N'False',@locallogin=NULL,@rmtuser=N'RPTSUSER',@rmtpassword='########'This definition seems to work fine (no errors). But when I look at the catalogs, hoping to see those on the remote server, I see the ones on the current server. What am I missing here? Thanks!

WHERE > CASE > IN

Posted: 07 Apr 2013 11:57 PM PDT

Hey,I'm using MONTH and YEAR functions to match on current month and current year. If the Month is 12 (December) I want to include Jan of the next yearThis is the current code:[code="sql"]WHERE MONTH(abr.ABR_Shift_Date) IN (MONTH(GETDATE()), MONTH(DATEADD(m, 1, GETDATE()))) AND YEAR(abr.ABR_Shift_Date) IN (YEAR(GETDATE()), YEAR(DATEADD(yy, 1, GETDATE())))[/code]Can I do anything this (not working):[code="sql"]AND CASE WHEN YEAR(abr.ABR_Shift_Date) < 12 THEN YEAR(abr.ABR_Shift_Date) IN (YEAR(GETDATE()), YEAR(DATEADD(yy, 1, GETDATE()))) ELSE YEAR(abr.ABR_Shift_Date) IN (YEAR(GETDATE())) END[/code]Thanks

Omnibackup.exe backup got failed while trying through this setup.

Posted: 22 Mar 2013 03:36 AM PDT

In SQL Server,to talk a backup for various databases from various server we are using omnibackup setup to execute SQL database backup not through SQL AGENT JOBS.This is script we are using,OmniBackup.exe -b D:\GenetecBackups -s (local)\OMI -d VideoSQL -MAX_BACKUPS 12(any error on this script pl let me know)We are getting error while executing this setup, BACKUP DATABASE failed with an application error.Error number on LOG : Error: 17836, Severity: 20, State: 17We are getting the above error when trying to back up the Security SQL databases (all servers).I have read this and not sure what size we would make this, any recommendations would be great.Kindly revert me with an good solution.Waiting for a valuable replies.Thanks in Advance friends.

Publishing and accessing SQL Server 2008 Reports

Posted: 31 Mar 2013 12:20 PM PDT

I 'm new to SQL Server database and I need help. I 'm trying to publish sql reports for users to access from the web. We have SQL server 2008 database with data, and I have installed a separate SQL server 2008 with Reporting Services on my windows xp machine. I have created data source from the remote sql server 2008 installed on a Windows Server, and publish the reports. I can access the report manager through the URL (//localhost:8080/ReportServer) on my xp machine browser. However, when I typed the same URL from another computer, I get page cannot be displayed message. What is going on? Does the report manager URL needs to be configured on the SQL Server that has the database instance? Please help. ThanksTo make myself clear, what I 'm trying to acomplish is to create and publish reports for users to access from their workstations. Any help will be appreciated.

tempdb

Posted: 08 Apr 2013 06:28 AM PDT

Hi,Our monitoring tool has reported that blocking on produtioni checked the detailsone user is running adhoc query on standalone database(not production database)one more query is running by the same user against tempdbSELECT SCHEMA_NAME(tbl.schema_id) AS [Schema], tbl.name AS [Name] FROM sys.tables AS tbl WHERE (tbl.name not like '#%') ORDER BY [Schema] ASC,[Name] ASCis this Intellisense feature? (which helps the user to make correct SQL Queries)one of production process (running aginst production db) is blocked by this user temp db query.So my question is user is trying to run adhoc query in standalone database and production db process is blocked. Just trying to understand how SQL works (because nothing in appln client or our server processes accesses this standalone databse)Please clarify.

How to NOT match on an exception list

Posted: 08 Apr 2013 05:10 AM PDT

If I have a table of Products that have a ProductName and I have a table of ProductNames that I don't want the first table matched to, what is the best process in which to do this? The names will be LIKE each other, but will not match exactly. When I pull data, I only want the ones from dbo.Products where they don't look like the Products from dbo.Exceptions. In my example, I want to pull back Products that don't look like WidgetD or WidgetE. Normally I would write: SELECT ProductDesc FROM dbo.Products WHERE ProductDesc NOT LIKE '%WidgetD%' AND ProductDesc NOT LIKE '%WidgetE%'The query should return WidgetA, WidgetB, and WidgetC (3 records)However, I don't want to jack with the code every time a new exception is added. Is there a better method? (example code below)CREATE TABLE [dbo].[Products]( [ProductDesc] [varchar](200) NULL) INSERT INTO Products(ProductDesc) SELECT 'WidgetA'INSERT INTO Products(ProductDesc) SELECT 'WidgetB'INSERT INTO Products(ProductDesc) SELECT 'WidgetC'INSERT INTO Products(ProductDesc) SELECT 'WidgetD'INSERT INTO Products(ProductDesc) SELECT 'WidgetE'CREATE TABLE [dbo].[Exceptions]( [ProductDesc] [varchar](200) NULL) INSERT INTO Exceptions(ProductDesc) SELECT 'Big WidgetD'INSERT INTO Exceptions(ProductDesc) SELECT 'Small WidgetE'

User interface

Posted: 07 Apr 2013 11:17 PM PDT

I am new to SQL server and currently using Access 2007. I am just wondering could someone attach a screen shot of what the user interface looks like for SQL server 2008?

Tracking a change to the notifications on an Agent job

Posted: 08 Apr 2013 01:34 AM PDT

Hi everyone,So I've had someone change the notifications, specifically the Page: nofitication on all agent jobs on our production server. It's easy enough to change this back to it's correct value but I'm wondering who did it. Do these get logged anywhere by default, or do I have to enable logging in some way to catch it in the future (which does me no good on this instance). Thanks

Joining with large table

Posted: 08 Apr 2013 01:29 AM PDT

Good morning. I have been working on a faster solution for some time, and thought I had it. I have a very large table with all people who work in the state. I need to join that table to a much smaller one of college graduates for outcomes. Since the graduates table will continue to grow, I can't remove anyone from the wage table. So wage table remains huge, and will only get bigger. Now, joining the tables is not a big deal, but for the fact that people often work in more than one job. But I created a table-valued function:CREATE FUNCTION dbo.GetWages (@SSN char(9))RETURNS TABLEASRETURN SELECT year, quarter, ssn, SUM(hours) hours, SUM(wages) wages, SUM(wages)/nullif(sum(hours),'0') rate FROM dbo.Wage_data WHERE ssn IN (@SSN) GROUP BY year, quarter, ssn;GOAnd it works great: SELECT student_id, LName, rateFROM dbo.Degrees_Data CROSS APPLY dbo.GetWages(SSN)However, we also want to know the industry of employment. When there are two jobs, we want the industry of the main job, defined as one with most wages. To get this, I have been using a CTE in a view, which is very slow since I cannot write in index to it. So again I wrote a TVF:CREATE FUNCTION dbo.GetWagesAndIndustry (@SSN char(9))RETURNS TABLEASRETURNWITH cte AS ( SELECT year, quarter, ssn, hours, industry , ROW_NUMBER() over (partition by year, quarter, ssn ORDER BY wages ASC) rn , SUM(wages) over (partition by year, quarter, ssn ) SumWage , SUM(hours) over (partition by year, quarter, ssn ) SumHours FROM dbo.Wage_data WHERE ssn IN (@SSN) )SELECT year, quarter, ssn, industry, SumHours hours, SumWage wages, SumWage/NULLIF(SumHours, '0') rageFROM cte WHERE rn = 1But this is also painfully slow. Eventually we want to be able to create reports on the aggregated data, but who's going to wait 5-10 minutes for a report to generate? Definitely, not my boss. There must be a creative way to group the wage data to one record per person, yet have the industry information still available. What am I not seeing? Thank you for any help.

Prepared vs AdHoc - Plan Cache ObjType

Posted: 08 Apr 2013 03:45 AM PDT

I would think that this would be an easy find with google... However, I have not found anything useful. So, gurus... What is the definition of a Prepared ObjType for a plan and what is the definition of an AdHoc ObjType for a plan; what distinguishes one from another and why is it important to me?

Link Server Connection

Posted: 07 Apr 2013 10:18 PM PDT

Sir, I am working in Medical college campus.They have 5 server in our campus. I have done a Port for SQL 1433 Both Server are activate an also Named Pipes Provider are Enable,TCP/IP is Enabled in Both Server.In Configuration Manager SQL Browser is running and SQL SERVER (MSSQLSERVER) also running.When connection Link server the following Error as occurred.TITLE: Microsoft SQL Server Management Studio------------------------------The linked server has been created but failed a connection test. Do you want to keep the linked server?------------------------------ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------Named Pipes Provider: Could not open a connection to SQL Server [53]. OLE DB provider "SQLNCLI10" for linked server "172.17.15.100" returned message "Login timeout expired".OLE DB provider "SQLNCLI10" for linked server "172.17.15.100" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 53)

Linked server

Posted: 08 Apr 2013 01:23 AM PDT

I created a linked server and use it to select data from one server to another.Is there another way except for linked server which will do the same job?

Searching stored procedure for table name but ignoring comments

Posted: 27 Nov 2010 08:43 PM PST

Hi All,Thanks to others who have posted helpful tips I have a way of searching through stored procedures to list those that reference a specific table (one way listed below ) but ideally I would like to exclude those table references that are listed in comments or hashed out and no longer part of the active body of the procedure ..Currently can't see a way to do this - has anyone got an idea on how to approach this or have actioned this themselves ? It may be this is just not possible searching through the sys tables ..but thought I would just ask Any feedback/tips much appreciatedThanks,Ralph SELECT DISTINCT SO.NAME FROM SYSOBJECTS SO (NOLOCK) WHERE SO.TYPE = 'P' AND SO.NAME LIKE @StringToSearch ORDER BY SO.Name

SQL to transpose data using the Formula from a Different table.

Posted: 07 Apr 2013 08:34 PM PDT

Hi Team,I have a Complex requirement.Source:-------- NAME PAYMENT_TYPE PAYMENTSUDHIR SAL 30.3SUDHIR ADV 10.3SUDHIR ALL_1 10SUDHIR ALL_2 10SUDHIR ALL_3 10MADHAV SAL 34MADHAV ALL_1 24MADHAV BONUS 10Formula:--------TGT_PAYMENT_TYPE PAYMENT_FORMULASAL SAL-ADVCOMM ALL_1+ALL_2-ALL_3BONUS SAL * 1.1Target Table:------------NAME SAL BONUS COMMSUDHIR 20 0 10MADHAV 34 10 24Please need help to code SQL Query / Package which will take the Formula on from the table Formula and apply on Source before pushing it to target.Thanks & Regards,Sudhir Nune.

Error while moving model database

Posted: 07 Apr 2013 11:32 PM PDT

I have moved the model database by using following method1. For each file to be moved, run the following statement.ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'G:\model\model.mdf' )ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'G:\model\modellog.ldf' )2. Stop the instance of SQL Server to perform maintenance.3. Move the file or files to the new location.4. Restart the instance of SQL Server or the server.While restarting instance of server i am getting following error.The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.How to solve this error?

Corruption - Renaming of system data databases to ldf :(

Posted: 02 Apr 2013 10:34 AM PDT

Hi,This morning a made a proper boo boo while building a new SQL Server 2008 R2 install. After the installation had completed I wanted to move the system databases to a new drive.However when moving the log files I executed the following T-SQL in error:ALTER DATABASE [master] MODIFY FILE ( NAME = master , FILENAME = 'J:\Log\SystemLog\mastlog.ldf' );ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'J:\Log\SystemLog\modellog.ldf' );i.e. I renamed the data files to ldf's.....As a result I cannot start the SQL Engine and get the following error in the ERRORLOG on startup:2013-04-03 10:12:40.74 spid9s Error: 5171, Severity: 16, State: 1.2013-04-03 10:12:40.74 spid9s J:\Log\SystemLog\modellog.ldf is not a primary database file.2013-04-03 10:12:40.77 spid9s Error: 945, Severity: 14, State: 2.2013-04-03 10:12:40.77 spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.2013-04-03 10:12:40.77 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.I would rather not have to re-install again. Does anyone have any advice on how to get around this one?Thanks in advance,JK

check condition

Posted: 07 Apr 2013 09:05 PM PDT

Hi all, I have to check a condition that if I entered correct first and last voucherno then only it have to perform some operation like insertion.... But I used IN then it will solve my purpose as if I enter @firstvoucherno correct and @lastvoucherno incorrent then also it will insert. Inspite of IN if I use and it will not returnign anything from my table example:SELECT * FROM GV_Booklet gv WHERE gv.BookletID = 'B00001' AND gv.BookletID ='B00003'Declare @FirstVoucherID ='B00001',@lastvoucherID = 'B00004'Here is part of my script:IF EXISTS(SELECT * FROM GV_Voucher gv JOIN GV_VoucherStatus gvs ON gv.VoucherStatusId = gvs.VoucherStatusIdWHERE gv.VoucherNo IN (@FirstVoucherID , @lastvoucherID)AND gvs.VoucherStatus = 'Dispatched')BEGINRAISERROR('Voucher No already in use',16,1)ENDELSEIF EXISTS (SELECT gv.VoucherNo FROM GV_Voucher gv JOIN GV_VoucherStatus gvs ON gv.VoucherStatusId = gvs.VoucherStatusIdWHERE gv.VoucherNo IN (@FirstVoucherID, @lastvoucherID )AND gvs.VoucherStatus = 'Active at HO')BEGIN INSERT INTO GV_StoreAllocation (StoreId, STNNo, FirstVoucherNo, LastVoucherNo, Quantity)VALUES (@storecode,@STNNo,@FirstVoucherID,@lastvoucherID,@quantity)END

Migration detail for SQL SERVER 2000 TO SQL SERVER 2012

Posted: 02 Apr 2013 09:12 PM PDT

HIi need help for migration the coding in sql server 2000 to sql server 2012 . regarding this what are the step taken from initial or any basic configuration need to setup before migration to step up step action to final stage please advise or guide mewith regardssankaripr

*MAY* need to switch DBs from Full Recovery to Simple, due to backup space issues...

Posted: 03 Apr 2013 02:52 AM PDT

First and foremost, I'm working with our backup admin to find a better solution for the problem, that won't require doing this.Second, adding capacity to the backup system would be nice, but by the time the request were to make it through the bureaucracy here, we'd be migrating to SQL3010...:hehe:So the scoop is, the backup system here ran out of room for the Sharepoint SQL DBs, and now myself and the backup Admin are trying to get it working again. She's limited in what she can do by the retention policies, I'm limited by the fact that it's Sharepoint, so no going in and dropping rows from big tables...(The Sharepoint Admin is on vacation, to boot...)So I think what may have set the problem off was the SP admin, after realizing that the previous admin had enabled auditing of EVERYTHING and did not set up the "trim" option to only keep a month or so worth of entries, tried to delete out old records. With the DBs being in Full recovery, the TLogs got rather full.Then the backup ran out of space because of this (I think the retention is 30 days, then it goes to tape)So my worst-case solution to reduce the size of data in the TLogs is to do the following:1. After-hours, switch all DBs from Full Recovery to Simple, sacrificing point-in-time recovery2. After SQL has "truncated" the logs, switch back to Full Recovery3. Let the early AM Full DB backup run, and get back on our bi-hourly TLog backupsYes we lose PiT recovery, but right now we have NO recovery since yesterday...Yes, I should have caught this sooner.So, a question:About how long might it be before SQL auto-truncates the logs? Can I "force" it to happen sooner by issuing a "Checkpoint" on the DBs, and could it take more than one checkpoint?I'm not currently planning to shrink the physical files, so that SQL doesn't need to "grow" the file later.Thanks,Jason(As an example, one of the Log files is showing 18655.19MB used in the log... I had been monitoring the sizes of the DBs and TLogs for a couple weeks, looking to free up some disk, and this DB averaged 402MB...)

HOW TO FIND A VALUE IN ALL COLUMN OF ALL TABLE IN A db.

Posted: 08 Jan 2012 04:58 PM PST

HI ALL,As i Have a task to find 9 digit value in all column of all table in a DB or All DB in SQL server 2005.SO DO ANY ONE HAVE THE T-SQL STATEMENT TO FIND IT?All Help is appreciated.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog