Tag Info

New answers tagged

4

If you haven't been capturing and storing those metrics somehow then there isn't a way to see history. However you can pretty easily see which tables are using the most disk now if that helps.


5

SQL Server doesn't track this information. You'd need some kind of monitoring (whether it be a tool or home-grown) that keeps snapshots of table space over time, going forward. This can be simple or complex: Viewing the report @Zane highlights in his answer, and saving the file every so often; Taking your own periodic snapshots of views like ...


2

You only need to SET NOCOUNT ON; once per procedure, preferably at the top of the body of the procedure itself. Certainly you'd need it prior to any statement that generates output. So, for instance, I'd use something like this as a template for creating procedures: CREATE PROCEDURE dbo.MyProc AS BEGIN SET NOCOUNT ON; .... END GO Books Online ...


0

Building upon Method 1 from KASQLDBA's response: Create a separate database to hold the backed up copy of the tables. Modify KASQLDBA's provided query so that you select from your original table and copy to the alternate database. SELECT * INTO MyNewDatabase.dbo.newtable1 FROM OriginalDatabase.dbo.originalTable1 --- For table 2 SELECT * INTO ...


1

That is because SQL Server reads entire pages instead of records and when a index is used SQL Server might need to follow the bookmark to fetch the fields that are not included in the index. So if your query isn't selective enough, it might end up reading all the pages of the index, and following the bookmarks to read all the pages of the actual table too. ...


0

You cannot backup specific tables to a .bak file, you can export them to csv or script them out or use bcp to put them into a file. What you can do if you want to back up specific tables (and they are always the same) is move them into a separate file group and back up that file group. See Back Up Files and Filegroups for the documentation. For example if ...


0

Backup Types are dependent on SQL Server Recovery Model. Every recovery model lets you back up whole or partial SQL Server database or individual files or filegroups of the database. Table-level backup cannot be created, there is no such option. But there is a workaround for this Taking backup of SQL Server table possible in SQL Server. There are various ...


0

Method 1: If you are only concerned with data in the tables and that to be used locally within the same database and server you can use below query to take backup of selected tables: SELECT * INTO newtable1 FROM originalTable1 --- For table 2 SELECT * INTO newtable2 FROM originalTable2 and so on... for n number of tables This statement WILL CREATE ...


0

There are 2 parts to this question, I'll treat them one by one. Modifying the schedule Modifying the schedule isn't really changing the maintenance plan as much as changing the SQL Agent schedule. Have a look at sp_update_schedule for this need. Modifying the Maintenance plan itself This will be hard using pure T-SQL as the maintenance plan is stored as ...


0

I think you might still be able to use the (employee_id, department_id) index, but you'd have to include a 'dummy' line in the where phrase, like: "employee_id = employee_id) having an index on (employee_id, departemnent_id), having to search / restrict only on a department_id knowing it won't use the index since wrong order (or things have changed by ...


0

The only way you can be sure one is not being used is to drop it. ;) I have monitored linked servers for weeks and then had someone complain within hours of dropping it. You should script it out first. Right click in Object Explorer and chose the appropriate option.


1

i know it's been a while since the question but i found a possible solution. This link provides an explanation and examples of how to use the connections available in the connection manager enabled for the Script Component in the SSIS https://msdn.microsoft.com/en-us/library/ms136060.aspx The portion of code that we are interested is this: ...


4

In case you are interested on running this kind of sort repeatedly, or on a larger dataset, you may want to consider adding a key table to control the sort order, and JOINing to that table. First, we setup the test environment: USE tempdb; CREATE TABLE dbo.K ( KOD VARCHAR(255) NOT NULL CONSTRAINT PK_K PRIMARY KEY CLUSTERED ); CREATE TABLE ...


2

Indexed views are always in sync with the data in the underlying tables. You can read more about indexed views here. Be careful going overly enthusiastic with indexed views: they can be a real performance killer on heavily written tables.


1

This will return Z15,B,C,D,E first in ascending order followed by the remaining Kod in descending order: ORDER BY CASE WHEN Kod LIKE 'Z15%' THEN 1 WHEN Kod LIKE 'B%' THEN 2 WHEN Kod LIKE 'C%' THEN 3 WHEN Kod LIKE 'D%' THEN 4 WHEN Kod LIKE 'E%' THEN 5 ELSE 6 END ASC, CASE WHEN Kod LIKE ...


0

Use a CROSS APPLY (or perhaps OUTER APPLY) to replace the case statement in your aggregates like this: with f as ( select * from ( values (1,4,1,0,0) ,(1,5,0,1,0) ,(1,6,0,0,1) )f(IncludeColumn,SecondInclude,Column1,Column2,Column3) ) select orgid=boardname ,'B' as OrgType , Lang , BoardName ,cast(Null as varchar) ...


0

A few notes here: You definetely have to separate the files logically using different partitions (I recommend separate partitions for: system, data files (mdf/ndf), transaction log files (ldf) and tempdb files (mdf/ndf). Where you put the tempdb transaction log file is up to you (tempdb drive or log drive), I'd go with log drive. Backup files should not be ...


2

Would this cause any impact on application side? Even if you run BPA or Upgrade Advisor, they are not going to catch everything that would potentially break your application (if it is using deprecated stuff). The best way is to take a full backup of your database in question, restore it on a test machine with newer compatiblity mode and do a regression ...


0

Start by checking your T-SQL syntax and reserved keywords. The differences between compatibility levels are documented in BOL: https://msdn.microsoft.com/en-us/library/bb510680(v=sql.105).aspx


0

Change the where clause to the following: where b.fld IS NULL You can think of a left outer join as connecting records from two tables together (a working table), forming a new table with columns from both tables on each record. All records from the A table are represented. Columns from the A table are populated, but where there is not corrresponding B ...


2

A cleaner way uses EXCEPT: SELECT a.fld FROM tbl1 a EXCEPT SELECT b.fld FROM tbl2 b Using the left join method: Select distinct a.fld from tbl1 a left join tbl2 b on a.fld= b.fld where b.fld IS NULL You're currently looking for records where a.fld= b.fld is true AND a.fld<> b.fld is true which, of course, is impossible.


2

Instead of using a JOIN on your table how about using NOT EXISTS: SELECT DISTINCT a.fld FROM tbl1 a WHERE NOT EXISTS (SELECT 1 FROM tbl2 b WHERE a.fld = b.fld); This will return all rows in tbl1 where the fld value doesn't exist in tbl2


2

Assuming "Table 1" is tbl1a, this should give you what you want: SELECT DISTINCT a.fld FROM tbl1 a LEFT JOIN tbl2 b ON a.fld = b.fld WHERE b.fld IS NULL


6

Sure : select * from database_n.dbo.Table_1 UNION ALL select * from database_n+1.dbo.Table_1 UNION ALL select * from database_n+2.dbo.Table_1 -- etc.


0

Removal as suggested in comments above. Will this work for you? select Invoice_TBL.ClientFullName ,InvoiceItems_TBL.StockID ,Stock_TBL.ItemCode ,Stock_TBL.ItemCategory ,Stock_TBL.ItemDescription ,Stock_TBL.Unit ,Stock_TBL.NoOfUnitsInACarton ,SUM(InvoiceItems_TBL.Quantity) as TotalQuantity ...


1

With this solution you can easily know which jobs are on the queue to run with a simple select to a table. Also, if the server goes down for some reason, cleaning the jobs that are waiting to run is as easy as a truncate on a table. And still if you want them to run you needen't do anything and they'll run orderly. Create a table (Say, ...


3

You can convert your string to XML and use a cast as xs:int ? to check if the value is an integer or not. declare @T table(C varchar(100)); insert into @T(C) values ('Widget (1234)'), ('Different Widget (123)'), ('Many Widgets (2x) (4567)'); select T.C, T2.X.value('(for $n in X return $n/text()[1] cast as xs:int ?)[1]', 'int') as Value from @T as T ...


3

If the data you need is always at the end of the string, would something like this work? declare @test varchar(200); set @test= 'Many Widgets (2x) (4567)'; select substring(@test, (len(@test) - charindex('(',reverse(@test))) + 2, charindex('(',reverse(@test)) - 2 ); We can reverse the string, find the ...


1

In more research and diving into the msdn site documentation I have found and am now using. sp_add_schedule, sp_attach_schedule and sp_detach_schedule, sp_delete_schedule so when the job sees that others are running ATM I am doing this: exec msdb.dbo.sp_add_schedule [MSDN link][1] @schedule_name = @TempScheduleName, @enabled = 1, @freq_interval = ...


1

Yep, indeed you can! So you are hoping to have one job complete and the last step of that job would be to kick off another job? Here is the command to kick off the next job: sp_start_job E.g. The first job has a schedule and final step of the job is to run: exec msdb.dbo.sp_start_job N'My Second Job'; "My Second Job" doesn't have a schedule, relying on ...


0

If you undo (ctrl-Z) after the wrong auto-selection appears, your original entry will be restored.


0

Many thanks to everyone here, everything was very helpful. Especially knowing about simple recovery backups. FYI we changed the log path to another partition which resolved the issue. Moving was another problem as I had to recover space from another partition create a new partition etc etc. Many thanks to everyone, FYI I tried voting both your answers as ...


0

You'll need to deny permissions on the master database Add a new role DenyExecRole on master DB Add the desire login to that role Deny execute permissions: USE master; DENY EXECUTE TO DenyExecRole; GO


0

I believe it should be something like: USE <database name>; DENY EXECUTE ON OBJECT::<object name> TO user name; GO OR DENY EXECUTE TO <user name> Alternatively you can create a DATABASE role and assign Table rights to this role then attach role to user: Create a new Database Role named appropriately. USE ...


5

That's easy: Select ACCT, SUM(BALANCE) from dbo.account_balances where ACCT IN ( 1000, 1001, 1002...) GROUP BY ACCT


4

The following SQL will show you which file groups your tables and indexes are in which will make it easy to see if there is data in any file group that shouldn't have data. SELECT f.[name] AS FileGroupName , o.[name] AS ObjectName , o.[type] AS [Type] , i.[name] AS IndexNAme , i.[index_id] AS IndexId ...


0

First do a DBCC LOGINFO(); then go to the very last row. Is the status 2? That means it's active and it cannot be shrunk until that VLF has released that portion of the log file so next checkpoint it could flush it to disk (assuming it has resources). If it is active you can do a select name, log_reuse_wait_desc from sys.databases to see what is using ...


0

As far as what we have received and tested the results on DBPROP_COMMANDTIMEOUT is that the default timeout of 30 seconds does not seem to work at database level, also when specified to any other value. Like for few specified a default of 30 seconds ran more than 50+ and thus neglecting the time out. So we believe SQL seems nothing to play a role in ...


0

The Connection timeout is always set by client. The timeout is normally configured when the connection is created. In order to prevent recompilation of client code the timeout interval is often stored in a config file, registry entry etc You cannot configure the timeout on the database itself.


0

SELECT PP.PRODUCT ,PP.START_TIME ,PP.END_TIME ,PP.PLANT_QTY AS PLANED ,COUNT(PAD.WORKING_TIME) AS ACTUAL FROM PRODUCT_PLAN AS PP LEFT JOIN PRODUCT_ACTUAL_DETAIL AS PAD ON PP.PRODUCT = PAD.PRODUCT AND PP.START_TIME<=PAD.WORKING_TIME AND PP.END_TIME>=PAD.WORKING_TIME GROUP BY PP.PRODUCT,PP.START_TIME,PP.END_TIME,PP.PLANT_QTY ...


3

The DMV's themselves are the same between same builds of SQLServer and give you a snapshot or a cumulative counter of the status of your server at any given time. You cannot restore the database to a new server with the metadata from the old one. You can make snapshots of some of the performance metrics into a table but you cant restore the state of the ...


0

A view and individual SQL logins would be appropriate if each patient accessed the database directly using an ad-hoc query tool, like Access. However, in the case of a front-end application, I suggest performing patient authentication in the app code (perhaps with individual user credentials stored securely in a database) and then just use the patient ...


1

You need to know who the user is and that information needs to be accessible through the view. CREATE VIEW dbo.tPatientForUser AS SELECT patient_id, name, address, gender, loginame FROM tPatient WHERE loginame = SUSER_SNAME(); Or, depending on your application you might need: WHERE loginame = ORIGINAL_LOGIN(); This can help you filter the rows to ...


1

You can ship the logs to as many sources as you want. Just add a new secondary server on the primary one. The impact will be on the Korean server as it will have to copy the logs from the backup directory and replay them.


0

The software installer is reading the %userprofile% or %homepath% environment variables to create the user database, you will have to change the installation script or temporary change the environment variables while running the installer. This has nothing to do with Express itself as it is creating the databases where it's asked to do so by the ...


7

since you are using SQL Server 2008 Web edition, your best bet is to use LOG SHIPPING. Your database size is 100GB, so initializing the secondary from a backup should be considered. Dont use GUI to set up initial logshipping. SO your steps should be : Backup 2008 Web edition database and restore it on 2012 enterprise WITH NORECOVERY. Launch Logshipping ...


2

The T-SQL way works. Thanks to http://zarez.net/?p=179 I found the SSMS way to do this (UI). To change the schema owner from Sql Server Management Studio: Expand your database -> Security -> Schemas In the Object Explorer Details you can see a list of the schemas and the owners: If you don't know what schema(s) the User owns, check the ...


3

(if someone finds a way - which I dont think exists, I will delete my answer :-)) Is there a way to determine if a backup contains CDC data? I dont think that there is any way to know from a backup, if CDC is enabled or not. The most you can know if the database was involved in replication (under the hood uses log reader agent that scans the ...


0

Using WMI to backup RS key might not be so efficient. You may try the below script that I found at http://powershell-tips.blogspot.com/2015/05/backup-report-server-encryption-key.html #------------------------------------------------------------------------------------------ # Script : Backup_RSKey.ps1 # Author : Som DT. # Purpose : Backup ...


1

Non-aligned indexes negate the primary benefit of partitioning which is switching partitions. If you do not depend on that and are partitioning for other reasons (different storage, read-only partitions, incremental stats, ...) then go ahead and create non-aligned indexex. Non-aligned indexes are useful because you can enforce unique constraints on the ...



Top 50 recent answers are included