Friday, August 2, 2013

[SQL Server 2008 issues] Tempdb tables two months old

[SQL Server 2008 issues] Tempdb tables two months old

Tempdb tables two months old

Posted: 01 Aug 2013 02:06 AM PDT

Hi AllI run the following query to get all tempdb tables:[code="sql"]SELECT *FROM tempdb.sys.sysobjectsWHERE name LIKE '#%'ORDER BY crdate[/code]I can see around 50 tables that are between one and two months old, and another 70 that are between one week and one month old.I'm guessing these are created by application components that maintain a constant connection, and are never dropped or deallocated. Is there a way I can find out what procedure, application, or even connection is creating these tables? I'd like to go into whatever is creating them and add the DROP or SET = NOTHING.Thank you

String esaping in TSQL

Posted: 01 Aug 2013 06:50 AM PDT

Hi guys,So after 3 days of dev I'm now collecting enough data from remote DBs to be able to knock up an OK web front end. The last part was the most annoying.I'm using openquery & exec(@Q) over my linked servers but for one script this proved very annoying. The query searches for job steps which recently failed that included a backup. It then searches the message column to find out which database was at fault. The problem is this requires a bunch of charindex/substring to get to it.In PL/SQL you can do: q'[some query]' and it will automatically escape all apostrophes between the square brackets. Does TSQL have similar? This is the kind of mess I was left with:[code]set @query = 'insert into backup_history (INSTANCE,DB_NAME,START_DATE,END_DATE,SIZE_MB,TYPE,STATUS)SELECT * from openquery(' + @server + ',''SELECT @@SERVERNAME server, SUBSTRING (sh.message,charindex(''''database '''''''''''',sh.message)+10,case when ((charindex('''''''''''''''', sh.message, charindex(''''database '''''''''''',sh.message)+10))-(charindex(''''database '''''''''''',sh.message)+10)) = -10then 0 else (charindex('''''''''''''''', sh.message, charindex(''''database '''''''''''',sh.message)+10))-(charindex(''''database '''''''''''',sh.message)+10) end) db[/code]As you may have guessed predicting the right number of apostrophes to include in each section was the main issue.

Differential backup performance, advantage and disadvantages

Posted: 01 Aug 2013 05:54 PM PDT

Now we are using full backup and log backup as a backup option for our sql server.We do full backup daily once and log backup every one hour. But now i am planning to use differential backup also.But i don't know much about its performance and speed and size of backup file. So i am planning to have full backup weekly once for less changing database and have differential backup everyday. I am not leaving transaction log backup as it will help in point in time recovery.Now i want to know is it differential backup will help me in anyway? What will be the size compared to log backup. Is there any disadvantage in using differential backup?

Question in SSIS package, data flow task.....

Posted: 01 Aug 2013 01:03 AM PDT

Hello,I am not sure how to proceed here... I will try to expline my situation clear....I need to write a SSIS package for Incremental download of data from Oracle source to SQL Server 2008 destination. With every download.. there might be combinations of Insert/Updates/Deletes.and there is a primary key column and a last_update column both in source and destination. What I want to do is.....Look at the maximum value of last_update in the destination table, then go to source and get the data which is one day before max(last_update) column..... (For ex: If max(last_update) in destination is 07/31/2013..... then I need to get all rows with last_update = 07/30/2013 from the Oracle source)...My question is: How can I get this done in SSIS package, may be it's already is there is google... but I failed to get it.....Thank you all for your replies.

Moving Records in Batches - 7 Tables - Several Million Records

Posted: 01 Aug 2013 09:00 AM PDT

Hello All,I know there are multiple ways to move records between tables but I'm trying to come up with a good way to go about moving millions of records from multiple tables into another set of tables. The reasoning for this move is pretty complicated but it's a necessity. Here are the basics,- 7 Tables, let's call them tables temp1 thru temp 7- Each Table has 1 million + records that need to be migrated to 7 tables within a different database, let's call those tables arc_temp1 thru arc_temp7- I would prefer to run this in batches overnight, say 1k or 10k per batch at a time- I would like the batches to go in order and then loop, i.e. move 10k from temp1, then move 10k from temp2, then move 10k from temp 3, etc. and then loop back to temp1 and start again- Although i need to move millions of records, this can be done over several nights and I would like to only run this for maybe 4 hours at a time I've done rowcount with inserts before but I don't know if this is the best way to go about it, i've also used cursors but with millions of rows I think this would be a not-so efficient way of going about it, i've never used SSIS to do a move job like this before but I could probably figure that out as well if you guys think it would be the best way.Also, you don't have to actually write any code, just a pointer in the right direction and I'll be able to figure it out.Thanks for the help

Moving Database That Has a Schema

Posted: 01 Aug 2013 01:04 PM PDT

Hi experts,I'm trying to move a 2008 database that has a named schema (not dbo) to another server.I restored from backup onto the new server. This database is the default for user abc. Login abc is also the owner of schema abc.On the new server the abc login this database uses works, sort of, but is unable to access the tables which are owned by the schema ie abc.Table1unless I explicitly add the schema to the Select statement like this:Select * from abc.Table 1 - this works.2 things are wrong: 1.Objects created by the abc login should automatically have the abc schema, but they don't - they have dbo2. This login cannot select data unless the schema is included in the Select statement - as Select * from abc.Table1. If I use Select * from Table1, I get "object not found".Can anyone tell me what's wrong please?I'm thinking of starting over.Should I pre-create the login BEFORE restoring or should I delete the login, then restore this db?Thanks, Jack

Need SQL Query

Posted: 31 Jul 2013 11:37 PM PDT

Dear All,need sql query for following scenario:Development server somebody is altering the table column or delete records,we need sql query for find the systemname and username.

Index Fragmentation and Page count minimum

Posted: 01 Aug 2013 08:21 AM PDT

I started writing rebuild index script.However, I came across about "SET @page_count_minimum" value .Could you please help me understand what is page_count_minimum and its value to be set etc.Thanks,

Dm_exec_procedure_stats rollback issues

Posted: 01 Aug 2013 05:21 AM PDT

Hi everyoneWas wonder if anyone encountered this issue querying sys.dm_exec_procedure_stats before.A user queried the sys.dm_exec_procedure_stats view and caused a system wide WRITELOG wait. We killed the query and is ended up still stuck in a Rollback for about 4 hours now.Any ideas on how to resolve this issue?

Using extended events to capture sort warnings and hash warnings

Posted: 01 Aug 2013 02:48 AM PDT

HiI have just downloaded the extended events gui for sql2008.Can anybody point me to any articles on how to set this up to capture the info for sort warnings and hash warnings ?

Can SSIS tell a batch file to move on before the SSIS package completes?

Posted: 01 Aug 2013 03:47 AM PDT

Hi,Question: Can I run a series of SSIS packages from a batch file where each package has 10 steps, and after a certain step (specified within each package) the batch file starts the next package up so that more than one package is running at once? Like, is there a task that basically says "send completion signal?" Or is there some other mechanism by which I can run a series of SSIS packages, setting a marker within each package that tells when to start the next one running asynchronously?Background:I have several SSIS packages that I run in a row. Most of them extract a bunch of data via Excel Business Warehouse add-ins, then load that data to SQL tables and massage the data. The Excel part of each package needs to run by itself as it runs on the local machine and I only want one Excel session to be open. As soon as the Excel part of each package is done (or in cases where there is no Excel then immediately), I would like the batch file to move on to the next package so that it starts while the previous one is finishing up.Here is a sample of what my batch file looks like:[code="plain"]set dtexec="C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec"%dtexec% /F "C:\DATA\SQL Packages\My 1st Package\My 1st Package.dtsx" /REPORTING EWCDI >>"C:\DATA\SQL Packages\My 1st Package_error.log"---------------------------------------------------set dtexec="C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec"%dtexec% /F "C:\DATA\SQL Packages\My 2nd Package\My 2nd Package.dtsx" /REPORTING EWCDI >>"C:\DATA\SQL Packages\My 2nd Package_error.log"---------------------------------------------------set dtexec="C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec"%dtexec% /F "C:\DATA\SQL Packages\My 3rd Package\My 3rd Package.dtsx" /REPORTING EWCDI >>"C:\DATA\SQL Packages\My 3rd Package_error.log"[/code]Thanks for the help!Tai

Right outer Join???

Posted: 01 Aug 2013 05:25 AM PDT

I know I'm lost somewhere here but I thought this would get me all client in Rday_All_clients even if a record didn't exist in R_Day ?[code="sql"]SELECT Rday_All_clients.LName, Rday_All_clients.FName, R_Day.OID, R_Day.STARTTIME, FROM R_Day RIGHT OUTER JOIN Rday_All_clients ON R_Day.ID = Rday_All_clients.Client_IDWHERE (R_Day.OID = '140FA7F3F5924873B8A14E369A3CCCBD') AND (R_Day.CAD69 = '1')ORDER BY Rday_All_clients.LName[code="sql"][/code]

Catch text before symbol

Posted: 30 Jul 2013 03:15 AM PDT

Hi geniuses,consider:[code="sql"]create table #mytable( Value varchar(50))INSERT INTO #mytable (Value) SELECT 'First.Second'[/code] [b]I need to isolate what's before the symbol: '.'[/b]I was able to catch whats after the symbol:[code="sql"] SELECT Value, (substring(Value,charindex('.',Value)+1,LEN(Value))) AS 'beforedot' FROM #mytable[/code]Thanks in advance!

WITH (NOLOCK), allows dirty reads.

Posted: 31 Jul 2013 09:04 PM PDT

Hello,[size="1"]I am aware the the WITH (NOLOCK) hint should be avoided, that is not the question. Also I am aware that WITH (NOLOCK) can result in inconsistend data. (Any Non serializable isolation level can result in inconsistend data, so I am not concerned about that).[/size]But:[b]Does using the WITH (NOLOCK) hint in queries allow dirty reads?[/b]Thanks,Ben

Backup Set List Not Populating

Posted: 01 Aug 2013 04:14 AM PDT

I have posted this on Amazon's Developer Forum, but I have also seen this happen at other times.I have a Windows 2008 server with SQL Server installed (image_1) on Amazon EC2. I create an image of that machine, then I launch the newly created AMI (image_2). When I go into (image_2)'s Management Studio to perform a database restore, I noticed that all of my backup result sets are not displaying, yet they are on disk.What it does look like: NoBackupSets.jpgWhat it should look like: ListedBackupSets.jpgWhat I was hoping that would happen is I could fire up image_2, then using the Management Studio's GUI, restore a database from a point in time. This would speed up the process greatly over having to do it all individually by script.My question is, when the backup set list is not populated, is there a way to populate it or do I have to run my restore manually from script?Thank you in advance,Jim

Query an Acceess Table in SSMS

Posted: 01 Aug 2013 01:16 AM PDT

Heres my scenario. I have an access table called Innovative in an access database.While on the server i can query this SELECT *FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', '\\vq1\Efile Document Storage\HR\HR\Employee File Notes\Employeefilenote.accdb'; 'admin';'',innovative); so i have made that into a view called bk_Innovative. When i want to query that from SSMS loaded on my machine not the server i get this error.OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine cannot open or write to the file '\\vq1\Efile Document Storage\HR\HR\Employee File Notes\Employeefilenote.accdb'. It is already opened exclusively by another user, or you need permission to view and write its data.".Msg 7303, Level 16, State 1, Procedure bk_Innovative, Line 6Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".im a network admistrator, i have access to that path, so why if i make that into a view on the server, cant i query that out on my pc. it only works to query on this server. I want to use this query in Reporting services. Any IDEAS?

Stored Procedure (or) SQL Script Output to Text File

Posted: 28 Apr 2009 09:16 PM PDT

Hi All, I am having a Stored Procedure Or SQL Script to be attached to Job Scheduler. When this Stored procedure executes it generates some output text. I need to store this output to text file when ever this store Procedure (or) SQL Script executed by job Scheduler.Please help me how to handle this?Thanks,Anil
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog