Microsoft SQL Server 2008

49K

Solutions

17K

Contributors

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Share tech news, updates, or what's on your mind.

Sign up to Post

An external provider has setup a load of SQL agent backup jobs on a 2008 database. Since this the MDF file seems to have grown from around 50GB to 120GB.

When i run a script to check the table usage, the total table usage is only 37GB. The bakups created by the agent were manually deleted from the drive as it had consumed 100% disk space.

Will anything have been left in the mdf file to cause it to grow so much and is there a procedure I can run to reduce its size.

Code I ran to check table size is as follows.

SELECT 
 t.NAME AS TableName,
 i.name AS indexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages, 
 SUM(a.used_pages) AS UsedPages, 
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB, 
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB, 
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM 
 sys.tables t
INNER JOIN  
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND  
 i.index_id <= 1
GROUP BY 
 t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
 OBJECT_NAME(i.object_id) 

Open in new window

0
I'm under a tight deadline (and have a moody manager who's not patient ) to remove some hardcoded .Net  code and create a table so we could read the data dynamically.

This is how the code looks like (there are 48 FieldIds...so 48 rows)
  Fields.Add(new ReportField() { FieldId = "1", TableName = _tbMasterLease, ColumnName = "LeaseNo", DisplayName = Normal.LeaseNo, DbType = DbType.String });

Open in new window



And this one (used for some boolean fields)
            Fields.Add(new ReportField
            {
                FieldId = "48",
                TableName = _tbUnit,
                ColumnName = "Returned",
                DisplayName = Normal.Returned,
                DbType = DbType.Boolean,
                Bool_ValueIfTrue = Normal.Yes,
                Bool_ValueIfFalse = string.Empty,
                Bool_TrueText = Normal.Yes,
                Bool_FalseText = Normal.No
            });

Open in new window


This is how I want to do it:


A table with columns that match each property in the code. So,
Identity field (this is the auto increment identity field)
FieldId (ex-developer hasn't used sequential Ids. I want to keep the same Id because we have data saved already with these Ids)

TableName
ColumnName
DisplayName
DBType
Bool_ValueIfTrue
Bool_ValueIfFalse
Bool_TrueText
Bool_FalseText

Should I add the Identity field? Any other ideas given that I have to get this done fast?
0
This is SQL 2014

We have a table with 3 Text fields. It holds data that has been compressed in .Net code. Not encrypted, it's compressed and .Net code decompresses the data to display on the screen.

It looks like this
r1.png
I don't know if we'll lose the data but can I change Text field to nvarchar(max) without losing data?
0
Hello,
I am trying to create a testing. Database1 instance of a database with SQL Authentication which was previously set on Windows Authentication and  dbo.
I have a Audit trial set on the StoredProcedures .
 The Audit Trial table (Table_Debug) has credential susername.

Query used in the Stored Procedure is
Insert into Table_Debug(sql)
After execution of the above query the entry in the table is like:

Table_Debug

SQL                                                Username  
"Select * from table1"                 "Network\Username"

Now after setting as the new database instance as  testing. Database1

The above query gives result as

SQL                                                Username  
"Select * from table1"                 "testing.Database1"


Any suggestion on how to get the user name who is connected to the Database.
Any change I can do on the credentials of the table.

Cheers
0
Hi there. We currently have SQL Server 2008 R2 running on a single server/DC (small office setting), and we just purchased a new server box, and want to move everything over to it (including SQL Server). Unfortunately, the installation media with the licence on it is missing - is there a way to find the licence that the old server with SQL 2008 R2 is using? To clarify, SQL was installed separately, so should have its own licence. I looked through the registry, but the "Productcode" registry key didn't have the correct format (wasen't aaaa-bbbbb-ccccc-ddddd-eeee).
Thanks.
0
I have a full backup every weekend, and every 30 min transaction log backup. Today I found out one transaction log backup fail last night but after 30 min, the transaction log backup runs again, it succeeded, then continue to run succeeded.

I want to know if I should go ahead to run a full backup to ignore the failed transaction log backup, no need to wait to weekend. My reason is I cannot restore all the logs if something happened now. I have to use last week's full backup + all the transaction logs before next weekend full backup. Since one of the transaction log failed, I am not able to restore to point in time, Am I right?
0
I have select statement the produces 9 columns

What I need returned is for columns 2 through 9
The results of the first column where the integer value > 0

example... the columns would be
IndividualID       [1]         [2]         [3]  ... etc
95487512         32546      0         33365
95487513             0         33358  33365

The desired final select would be
IndividualID        OPCol
95487512            32546
95487513            33348
0
I have a sql script that writes the selection into xml format. I am trying to write a batch file that will output this data into an xml file. Using :XML ON I am receiving the following error in the output file:

<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?>
HResult 0x80004005, Level 16, State 1
No description provided

my .bat file code:
sqlcmd -i "ProductHierarchy-Dept-Cat-Subcat.sql" -S "SC-L-WINSQL-001" -d "HQ_L" -o "output.xml" -y0

If I turn :XML OFF it prints out the entire data selection into one line.

SET NOCOUNT ON 
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON

--drop tables if exist
If(OBJECT_ID('tempdb..#importType') Is Not Null)
Begin
    Drop Table #importType
End

--select data
If(OBJECT_ID('tempdb..#AMSprocuctHierarchy') Is Not Null)
Begin
    Drop Table #AMSprocuctHierarchy
End

select 
	distinct DIM.pos_dept as Department, 
	SD.DESCRIPTION as DeptDesc,
	--left(DIM.category,4) as Category, 
	--right(rtrim(DIM.category),4) as Subcategory,  
	DIM.upc as UPC, DIM.description as ItemDesc, 
	substring(DIM.CATEGORY, 1, 4) as Category, 
	substring(DIM.CATEGORY, 5, 8) as SubCategory, 
	CAT.DESCRIPTION as CategoryDesc,
	subcat.DESCRIPTION as SubcatDesc
		into #AMSprocuctHierarchy
			from DHAT_ITEM_MASTER DIM
			join store_department SD on DIM.pos_dept = SD.STORE_POS_DEPARTMENT
			join CATEGORY subcat
			   on subcat.CATEGORY = DIM.CATEGORY
			join CATEGORY cat
			   on 

Open in new window

0
Upgrading an old e-commerce website from SQL 2000 to SQL 2008 (a necessary intermediary step) and finally to SQL 2016 a query on a search page has significantly slowed down.  There is one search criteria text object for the users to enter their search data.  Multiple tables/columns are searched based on the search criteria.  If I eliminate the final OR statement in the where clause the query takes 1-2 seconds.  Leaving the final OR statement adds 20 - 30 seconds.  Any suggestions on how to speed this up.  I have checked the SQL 2000 tables and there are no indexes, so I'm not sure why this worked faster in SQL 2000.  

Final OR statement mentioned:
OR (select TOP 1 CONVERT(varchar(500),cha.Answer) from WebCheckoutAnswers as cha, WebCheckoutQuestions as chq    
      where cha.keyWebCheckoutQuestions = chq.keyWebCheckoutQuestions    
      and cha.keyWebOrder = convert(varchar(256),o.KeyWebOrder)
      and chq.WebOrderColumnName like 'ShowName') LIKE '%12281%')


SELECT  top 15 o.KeyWebOrder AS id,
convert(varchar(256),KeyWebOrder) AS KeyWebOrder,
OrderNumber AS OrderNumber,
DateCreated AS DateCreated,
convert(varchar(256),keyWebStatus) AS keyWebStatus,
LTRIM(RTRIM((SELECT TOP 1 CONVERT(varchar(500),Answer) FROM WebCustomerAnswers as cha, WebCustomerQuestions as chq              
      WHERE cha.keyWebCustomerQuestions=chq.keyWebCustomerQuestions and cha.keyWebOrder=o.KeyWebOrder and cha.OrderType=1              
      and chq.WebOrderColumnName like 'BillingFirstName')) )        …
0
VMware vCenter 5.5 web login  coming Empty inventory Error reflecting is : "Could not connect to one or more vCenter Server systems: https://xxx.xxx.xxx.xxx:443/sdk"

VC 5.5 is running on windows 2008 and database is Sql2008  running on other server [both are vm running on esxi same host]
Telnet is Fine
DB Test is Fine
Services are fine
But Noting visible under vc inventory.
Did Server reboot and issue got fixed but repeated again after 4 days.

Pls help to locate if issue is with Network/DB/SSL or VC itself


Not able to locate much detail to apply appropriate fix
-2618423BBF69F68A.jpg
-351E50B996600DC9.jpg
-218B3348C6B2659B.jpg
-7057366E756B0F4D.jpg
0
I have a text field in a table and I want to read them. I did this but I still see a value like this
4sIAAAAAAAEAE2PT0sDMRDFv0qZ8x52W//U3LZKRcQt2OJFPAz

 r1.png
0
Hi Guys,

select hashbytes('SHA2_256','123'+'456'+'1') -- Hashbytes will always give UNIQUE value for n number of rows.

Just wanted to know, if we convert the HASHBYTES function to BIGINT like:-

select convert(bigint, hashbytes('SHA2_256','123'+'456'+'1')) will this conversion will always give UNIQUE values if their are million or billions of rows?

Please help.
0
Good morning,

I am having a problem running SSIS package in SQL 2016. I can create and run it in separate Import/Export Data utilities, but when I execute it in SQL itself I am getting an error message "Parser NULL". I also can not run the Import/Export wizard withing the SQL database engine. I am getting an error: "The SSIS Data Flow Task could not be created. Verify that DTSPipeline.dll is available and registered."

Please advise. THanks
0
Hello ,

I am being part of a project based on Citrix Migration 6.5 to 7.15 LTSR.
We have planned to migrate SQL 2008 r2 to 2012 sql.
we have been working on 2016 dev environment.

please provide me the scripts for the above two cases.

Thanks,
Shirish
0
Hi All,

I have below code.

 UPDATE A
		SET A.NilaiAlokasi = ISNULL(B.TotalNilaiAlokasi, 0)
		FROM TDMONEYPAYDIST A
		INNER JOIN
		(
		SELECT
		KodePembayaran
		, SUM(NilaiAlokasi+NilaiLebih) AS TotalNilaiAlokasi
		FROM TDALCAP A
		WHERE EXISTS
		(
		SELECT
		NULL
		FROM TDALCAP B
		WHERE A.NoAlokasi = B.NoAlokasi
		AND B.NoAlokasi = @NoTransaksi
		)
		GROUP BY
		KodePembayaran
		) B

		ON A.ID = B.KodePembayaran

Open in new window


It is using INNER JOIN, but I think it is not correct, but if I change to LEFT JOIN, then it should not have correct data update.

What should I do ?

Thank you.
0
Using SQL 2008

I have a query that's not working for me

DECALRE @scan @varchar(10) = '1147'
DECALRE @email @varchar(10) = ''
DECALRE @search @varchar(10) = ''


select *
from etc
where (scanlocation like '%'+ @scan + '%' or emailaddress like '%' + @email + '%' or fidlocation like '%'+ @search +'%')

I use like because sometimes we only get part of the whole of the string we are looking for.
I want to use different combinations of data in the parameters, so sometimes run it with all three populated,
sometimes two and one blank or as above with one populated and the other two blank. If I run the query above I get all records back
instead of records where scnalocation = '1147'
0
Trying to pivot results
keeping getting the message that the list contains more items
Can you review to see what i am missing?

CREATE table #GCTotalStatusList( gcvalue int, gcrole int)

INSERT INTO #GCTotalStatusList
            (gcvalue,gcrole)
SELECT  
            COUNT(case when u.active in (0,1) then 1 end) as ttlcode,
            COUNT(case when u.active =0 then 1 end)  as active_counts,
            COUNT(case when u.active =1 then 1 end)  as inactive_counts,
            COUNT(case when u.active in (0,1) and u.orderroletype  IN ('Housestaff','Fellow') then 1 end ) as HSttl_counts,
            COUNT(case when u.active =0 and u.orderroletype  IN ('Housestaff','Fellow')  then 1 end)       as HSactive_counts,
            COUNT(case when u.active =1 and u.orderroletype  IN ('Housestaff','Fellow') then 1 end)        as HSinactive_counts

      FROM   SCM.CV3SecurityGroup sg join SCM.cv3usersecuritygroup usg (nolock) on usg.SecurityGroupGUID = sg.GUID
    join CV3User u (nolock) on u.guid = usg.UserGUID
    WHERE sg.code = 'Growth Charts'
      Group by sg.code, u.active, u.orderroletype
      
      SELECT  *
           
            FROM    
            (
            select
            *
            FROM #GCTotalStatusList
            )d
            Pivot(Max(gcvalue )for gcvalue in (GCTotal, GCActive,GCInactive,HSTotal,HSactive,HSInactive)

            )GCpiv
0
Using a CTE to get totals, would like assistance on adjusting CTE to concatenate multiple rows
I want the output in the following format:
GC total: 177  36/141 HS total: 10/10
These results are for totals as well active and inactive

;WITH
gcttl AS (
            SELECT  COUNT( sg.code)  as gcttl                                      
            FROM   SCM.CV3SecurityGroup sg join SCM.cv3usersecuritygroup usg (nolock) on usg.SecurityGroupGUID = sg.GUID
            join CV3User u (nolock) on u.guid = usg.UserGUID
            WHERE u.active IN (0,1) and sg.code = 'Growth Charts'
            ),
gcttlinactive as
            (
            SELECT  COUNT( sg.code)  as inactivecode                             
            FROM   SCM.CV3SecurityGroup sg join SCM.cv3usersecuritygroup usg (nolock) on usg.SecurityGroupGUID = sg.GUID
            join CV3User u (nolock) on u.guid = usg.UserGUID
            WHERE u.active =0 and sg.code = 'Growth Charts'
            )
 , gcttlactive as
            (
            SELECT  COUNT( sg.code)  as activecode                               
            FROM   SCM.CV3SecurityGroup sg join SCM.cv3usersecuritygroup usg (nolock) on usg.SecurityGroupGUID = sg.GUID
            join CV3User u (nolock) on u.guid = usg.UserGUID
            WHERE u.active =1 and sg.code = 'Growth Charts'
             )
, hsttl as
            (
            SELECT  COUNT( sg.code)  as "hsttlcode"                        
            FROM   SCM.CV3SecurityGroup sg join SCM.cv3usersecuritygroup usg (nolock) on usg.SecurityGroupGUID = sg.GUID
            join CV3User u (nolock) on u.guid = usg.UserGUID
            WHERE (u.active IN (0,1) and sg.code = 'Growth Charts' and …
0
I have a SQL statement like this

SELECT DISTINCT SiteAddress,sitecity,SiteState,SiteZip,SiteCountryCode

 FROM table
WHERE (Deleted =0
AND SiteCountryCode = 'USA')
or (SiteAddress != NULL
and SiteAddress != 'NULL')

Open in new window


SiteAddress is nvarchar(256)

Howcome this SQL brings back NULL in siteaddress?

s1.png
0
I have a failed SQL backup and have no clue why this is happening? Can anyone help with this?

Failed:(-1073548784) Executing the query "BACKUP DATABASE [JDE910] TO  DISK = N'\\\\x.x.x.x..." failed with the following error: "Cannot open backup device '\\\\x.x.x.x\\JDE Images\\SQL Backup\\Daily\\JDE910_backup_2018_07_14_220001_8461617.bak'. Operating system error 1311(There are currently no logon servers available to service the logon request.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


X.X.X.X are the IP address of the webserver jde
x.x.x.x is the ip address of the NAS device where the backup has to be moved
0
Hi

This is how query looks like:
First query:

select FullName, SystemUserId from SystemUser
ORDER BY FullName

This query will show 100x systemuserID now to the next one:

update UserEntityUISettingsBase
set RecentlyViewedXml = null
where OwnerId = 'SystemuserID'

ok here comes the question, how do I do this easiest? Declare a var for 100 systemuserID? how do i do this so the 100x systemuserID updates easiest.

Thanks
0
Hello,
Can you please help,
I need to get the [Driver_In] Minimum Date Time/ [Driver_Out] Maximum Date Time between 2 tables (Per Driver) , then calculate the difference (Hours and Minutes),
This is What I have so far (But , it is wrong, I'm getting 2 lines per Driver

Select PickUpDriver AS [Driver], (Select Name From Drivers Where Drivers.DriverNumber = ActiveOrders.PickUpDriver) AS [Name],
CONVERT(date, OrderDate) AS [Order_Date],MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)) AS [Driver_In],Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME)) AS [Driver_Out],
DATEDIFF(second, MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)), Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME))) / 3600.00 AS [Hours_Worked],
DATEDIFF(minute, MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)), Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME))) AS [Minutes_Worked]
From ActiveOrders
where (((OrderDate > = DATEADD(d,0,DATEDIFF(d,0,GetDate())))  And (OrderDate <= DATEADD(s, +86399, DATEADD(d,0,DATEDIFF(d,0,GetDate())))))) AND Cast(datepart(yyyy,PickedUpTime)as varchar) + '-' + RIGHT('0' + RTRIM(MONTH(PickedUpTime)), 2) + '-' + Right('0'+cast(datepart(DD,PickedUpTime)as varchar(2)),2) = Cast(datepart(yyyy,DeliveredTime)as varchar) + '-' + RIGHT('0' + RTRIM(MONTH(DeliveredTime)), 2) + '-' + Right('0'+cast(datepart(DD,DeliveredTime)as varchar(2)),2)
And ((Select Name From Drivers Where Drivers.DriverNumber = ActiveOrders.PickUpDriver)
0
Dear expert,

Simple query, I want MSSQL to exclude all the NULL result in columns from the table.

Any easy solution?
Thx
0
I have a SQL 2008 R2 database that has needed a shrink for years but everyone was afraid.   It currently has allocated 688GB and only using 217GB.  This will be moving to a 2016 SQL server and I don't want to have all the unnecessary space moved.  
I have tested restoring the DB to the 2016 SQL and running the shrink to capture how long it takes.  (about 13 hours)   I then test the database on the 2016 SQL and find no issues,  
Now to do it live, I need to shrink the DB on it's production 2008 R2 SQL and no way to test to see how long it takes.  Compatibility doesn't change so there shouldn't be any data issues as well.
This is on a production cluster so I don't have another 2008 R2 SQL instance to test with on  the cluster to determine if it will take longer on the 2008 R2 instance.
Any known factors would help.
0
I am getting this error; 'Could not find stored procedure 'UP_UPDATE_LOG.'' on two different servers with two separate instances of SQL. One instance is 2008r2, and the other is 2016.

I am logged in as SA for the following steps
     1)  I tried to execute the sp, but I get the error; 'Could not find stored procedure 'UP_UPDATE_LOG.''
          a. I even tried this with; execute dbname.dbo.UP_UPDATE_LOG
     2)  I tried to create the sp, and I get this error;  There is already an object named 'UP_LOG_UPDATE' in the database.
     3)  I tried to drop the sp, and I get; Cannot drop the procedure 'UP_UPDATE_LOG,' because it does not exist or you do not have permission.
     4)  I tried to alter the sp, and I get; Commands completed successfully.
     5)  So I tried steps 1 to 3 and got the same errors as before


Any suggestions of what is going on and how to fix this issue?  and this looks like it is only happening to this one stored procedure.

Thank you,

Rich
1

Microsoft SQL Server 2008

49K

Solutions

17K

Contributors

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.