Size Of Returned Dataset?
Jan 10, 2008
I'm using a SqlDataSource with a GridView to look at the contents of a table. I'd like to display the number of rows returned by the query in a label above the grid. How do I get a hold of this value? I can't find where it's exposed through the DataSource or the Grid. I can get the number of rows in the currently displayed page, but that's not the value I'm after.
View 6 Replies
ADVERTISEMENT
Nov 21, 2006
I have a web page that has been in production for over a year. On occasion the web app users report sporadic errors which last a few minutes then go away. The page loads up a generic DataSet, and then that DataSet is assigned to a datagrid on the page. The error is that a column that is expected to be in the dataset is not found. I know this column must exist in the database because its a required field. After we reset IIS, then all returns to normal and the errors go away. Below is the stack trace.
Exception: System.IndexOutOfRangeException Message: Cannot find column LastName. Source: System.Data at System.Data.DataTable.ParseSortString(String sortString) at System.Data.DataView.UpdateIndex(Boolean force) at System.Data.DataView.SetIndex(String newSort, DataViewRowState newRowStates, DataFilter newRowFilter) at System.Data.DataView..ctor(DataTable table, String RowFilter, String Sort, DataViewRowState RowState) at MySystem.Web.GuestSearch.ShowResults(DataSet ds, Int32 currentPageIndex) at MySystem.Web.GuestSearch.LoadResults(Int32 currentPageIndex) at MySystem.GuestSearch.btnSearch_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain()
Our DBA can find no problems on the database side and our web servers don't log any system event problems either. I do not understand why resetting IIS helps the issue. I need to find the real cause of the problems rather than just resetting IIS all the time. Any ideas?
View 1 Replies
View Related
Sep 13, 2006
I am working on a company sign out sheet for our Intranet. The application accesses a sql database. I tried using a datareader as well as a dataset to access a small piece of information regarding the group that each employee belongs to in the company. My last attempt was to assign the value to the text property of a text box and evaluate the text from the text box with the Select Case but that did not work either.The application is supposed to generate an email to our receptionist as well as to the group technical assistant responsible for each group according to the value that is passed into the Select Case statement. With the datareader as well as the dataset and even now with creating a text box and accessing the text property, I was able to assign the group value to a variable (I can see the value in the subject of the email), but when the variable is supposed to be evaluated by the select case statement it skips right through all of the cases to case else and uses only the receptionist's address in the email as if it doesn't even see the value of the variable. I have searched for a possible answer to the problem I am but so far have had no luck. Any ideas? I included the part of the code that I am having trouble with: '********************************************************************************************' New DataSet is created to determine the group the employee belongs to so that the appropriate' group technical assistant is emailed when employee signs out'******************************************************************************************** ' declare variablesDim sqlGroup As String ' string variable to store sql statement for datasetDim BLGroup As String ' string variable to store the bl group of the employeeDim emailAddress As String ' string variable to store resulting email address Dim groupDS As DataSet ' dataset variableDim groupAdapter As SqlDataAdapter ' sql data adapter variable' sql statement to access group value from the databasesqlGroup = "SELECT BLGroup FROM BLGroupsView WHERE (RTRIM(fname) + ' ' + LTRIM(lname)='" & employee & "')" ' repopen the connection (leftover from working with the datareader)Connection.Open()' create new SqlDataAdapter applying sql statement and connectiongroupAdapter = New SqlDataAdapter(sqlGroup, Connection)' create new dataset groupDS = New DataSet()' populate the datasetgroupAdapter.Fill(groupDS, "BLGroupsView") ' get the value stored in the BLGroup column (only one row is returned at a time) BLGroup = groupDS!BLGroup' Create GroupTextBox TextBox control.Dim GroupTextBox As New TextBox()' Set options for the UserTextBox TextBox control.GroupTextBox.ID = "GroupTextBox"GroupTextBox.Visible = "False"GroupTextBox.Text = BLGroup' just trying anything with this next line, even when i didn't set this the select case statement did not seem to see the value returned by the variableGroupTextBox.runat = "server" ' use a select case statement to evaluate the value of the group (didn't work when I passed in the BLGroup variable eitherSelect Case GroupTextBox.TextCase "bh"emailAddress = "receptionist@myaddress.com; bhassistant@myaddress.com"Case "env"emailAddress = "receptionist@myaddress.com; envassistant@myaddress.com"Case "sw"emailAddress = "receptionist@myaddress.com; swassistant@myaddress.com"Case "fac"emailAddress = "receptionist@myaddress.com; facassistant@myaddress.com"Case "www"emailAddress = "receptionist@myaddress.com; wwwassistant@myaddress.com"Case ElseemailAddress = "receptionist@myaddress.com"End Select '*************************************************************************************************' set new message objectDim myMessage As System.Web.Mail.MailMessage = New System.Web.Mail.MailMessage()'**********************************************************' set message properties'********************************************************** myMessage.From = "me@myaddress.com"myMessage.To = emailAddressmyMessage.Subject = employee & " Signing Out at " & TimeOut.Text & " " & BLGroupmyMessage.Body = employee & " Signing Out at " & TimeOut.Text & " for " & Destination.Text & " will potentially return at " & EstTimeIn.Text'***********************************************************' set smtp server namesmtpMail.SmtpServer = "mailserver1"' send email using smtpsmtpMail.Send(myMessage) It sends the message and returns the BLGroup value everytime but only sends to the receptionist. Thanks for your time!
View 1 Replies
View Related
Jun 14, 2007
I have an existing call to a webservice that updates an MDB with data from a dataset.
I was now moving to a process using SQL Compact Edition sdf instead of an mdb.
I belive I have to DROP/Truncate the table in the SDF file, then create a new table then somehow insert the results of the retrieved dataset into the new table. DOes anybody have any pointers to help me out?
Here is my old way of doing it, using access.
Thanks.
Private Sub retrieveData(ByVal intMode As Integer)
'This process will call a webservice passing a parameter which in turn executes a SQL stored procedure, returning a dataset
Dim MyObj As localhost.Service1 = New localhost.Service1
Dim ds As DataSet
ds = MyObj.RetrieveSQLdata(txtSecureString, intMode)
Dim myConnection As OleDbConnection
Dim cmd As OleDbCommand = New OleDbCommand
Dim Connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & FileName & ";Persist Security Info=False"
myConnection = New OleDbConnection(Connstr)
myConnection.Open()
cmd.Connection = myConnection
Dim mystr As String = ""
For Each row As DataRow In ds.Tables(0).Rows
mystr = ""
mystr = mystr & "INSERT INTO CustMast ( "
mystr = mystr & "StoreNo, CustNo, ShipNo"
mystr = mystr & "values("
mystr = mystr & "'" & row("StoreNo").ToString & "', "
mystr = mystr & "'" & row("CustNo").ToString & "', "
mystr = mystr & "'" & row("ShipNo").ToString & "')"
cmd.CommandText = mystr
cmd.ExecuteNonQuery()
Next
System.Windows.Forms.Application.DoEvents()
cmd.Dispose()
myConnection.Close()
myConnection.Dispose()
End Sub
View 1 Replies
View Related
Mar 30, 2004
hi all
Any day, another question. I love you guys.
I want to select a subset of data from a dataset returned by either another subquery or a union.
e.g. this is how i would do it in oracle, but i have no idea how this can be done in mssql or whether it's possible at all.
select * from
(
select col1, col2, col3 from table1
union
select col1, col2, col3 from table 2
)
where col1 = 'blah'
in essence oracle treats the data returned by the subquery as a table that it would select from.
how would i do the same in mssql?
thank you
James :)
View 5 Replies
View Related
Jan 12, 2007
In one of my projects, i have been using the SSRS web service to render a report as pdf and then manually emailing it out.
I need to know whether the data set for the report returned any data or not (so that i can avoid emailing the report with a blank report body).
Ofcource this can be done by executing the same query (same as the query for the report) from C# code and checking the count of the resultset, before calling the SSRS Render method. But it might not be an efficient method.
Was wondering the the SSRS Web Service provides an property or method that can let me figure out whether the resultset was empty or not (after the report execution).
Thanks.
View 1 Replies
View Related
Aug 9, 2015
Right now I have the following expression in mind.
=IIF(Fields!CoolantLevel.Value <> "", "CoolantLevel:" & Fields!CoolantLevel.Value, Nothing)& vbcrlf &
IIF(Fields!Horn.Value <> "", "Horn:"& Fields!Horn.Value, Nothing)& vbcrlf &
IIF(Fields!DriveBrakes.Value <> "", "DriveBrakes:"& Fields!DriveBrakes.Value, Nothing)& vbcrlf &
IIF(Fields!ExhaustSystem.Value <> "", "ExhaustSystem:"& Fields!ExhaustSystem.Value, Nothing)& vbcrlf &
IIF(Fields!Turntable.Value <> "", "Turntable:"& Fields!Turntable.Value, Nothing)& vbcrlf &
IIF(Fields!SoftTouchLimit.Value <> "", "DriveBrakes:"& Fields!SoftTouchLimit.Value, Nothing)
I need the tablix that is not displaying data not to be as big as the one's that are in lighter terms.
Here is an imageÂ
How do I decrease the size of the tablix if no data is displaying so it co inside's with the rest of the tablix as in this image.
I haveÂ
1. Right-click the tablix and select Properties.Â
2. Click Visibility.
3. Select Show or hide based on an expression.
But I return, A hidden expression in textbox returned a datatype that is not valid.
I don't want to hide or show based on the result I just require that the tablix that doesn't return data stay's in proportion with the rest.
View 3 Replies
View Related
Nov 30, 2007
Greetings:
I have a database that is 1.7terabyte in size with 136gb free and throws a "transport level error" telling me to discard the results when I run dbccshrinkfile ('DBNAME', size). I have tried various increments of size, from truncateonly to 1MB below its current value, and nothing works. I have tried to detach and reattach the db, restart the service, restart the server, and none have provided a solution. Any ideas?
Regards,
Derek
View 1 Replies
View Related
Sep 16, 2007
I was working on the SSRS Report Designer and was trying to copy and paste a huge SQL query from SQL Management Studio (like I always do) to the dataset window.
This method usually works, but for this extremely long SQL query with nested SELECTs and JOINs, it seems as though the Report Designer dataset query window limits the number of characters including carriage returns I can input in it.
When I paste my SQL query from SQL Management Studio, it seems to paste only three quarters of the query, and thereafter I am not able to manually key in anything else or even make a line feed in the dataset query window.
Is there a limit to the number of characters I can key in here ? If so, is there any workarounds or configurations I can try ?
Kenny
View 11 Replies
View Related
Jun 20, 2006
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList);
13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
View 2 Replies
View Related
Sep 4, 2007
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
View 1 Replies
View Related
May 26, 2015
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
View 0 Replies
View Related
Oct 1, 2015
I have a small number of rows in a dataset, Table 1. There is a CLOB on a large dataset, Table 2. They join on a PK. I would like to retrieve this CLOB and add it to the data flow for Table1. In short I want to emulate the following:
Table 1:Â Small table without CLOB, 10 rows.Â
Table 2: Large table with CLOB, 10,000,000 rows
select CLOB
from table2
where pk = (select pk from table1)
I want this to return the CLOBs for the small number of rows in Table 1. The PK is indexed obviously so it should be a fast look up.
Table 1 and Table 2 live on different Oracle databases. How do I perform this operation efficiently in SSIS? It seems the Lookup and Merge Join wont do this.
View 2 Replies
View Related
May 27, 2015
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.
I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
View 3 Replies
View Related
May 21, 2007
I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking
View 2 Replies
View Related
Oct 12, 2007
Is there any way to display this information in the report?
Thanks
View 3 Replies
View Related
May 7, 2008
Hi,
I have a stored procedure attached below. It returns 2 rows in the SQL Management studio when I execute MyStorProc 0,28. But in my program which uses ADOHelper, it returns a dataset with tables.count=0.
if I comment out the line --If @Status = 0 then it returns the rows. Obviously it does not stop in
if @Status=0 even if I pass @status=0. What am I doing wrong?
Any help is appreciated.
ALTER PROCEDURE [dbo].[MyStorProc]
(
@Status smallint,
@RowCount int = NULL,
@FacilityId numeric(10,0) = NULL,
@QueueID numeric (10,0)= NULL,
@VendorId numeric(10, 0) = NULL
)
AS
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
If @Status = 0
BEGIN
SELECT ......
END
If @Status = 1
BEGIN
SELECT......
END
View 4 Replies
View Related
Apr 11, 2008
i have two datasets.one dataset have old data from some other database.second dataset have original data from sql server 2005 database.both database have same field having id as a primary key.i want to transfer all the data from first dataset to new dataset retaining the previous data but if old dataset have the same id(primary key) as in the new one then that row will not transfer.
but if the id(primary key) have changed values then the fields updated with that data.how can i do that.
View 4 Replies
View Related
Dec 19, 2006
Hi,
I have two datasets in my report, D1 and D2.
D1 is a list of classes with classid and title
D2 is a list of data. each row in D2 has a classid. D2 may or may not have all the classids in D1. all classids in D2 must be in D1.
I want to show fields in D2 and group the data with classids in D1 and show every group as a seperate table. If no data in D2 is available for a classid, It shows a empty table.
Is there any way to do this in RS2005?
View 2 Replies
View Related
Jun 15, 2006
I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
I'll try to post the full log in a new post.
View 11 Replies
View Related
Sep 3, 2015
Using this IIF statement:
=CountDistinct(IIF(Fields!Released_DT.Value = Fields!Date2.Value, Fields!Name.Value,
Nothing))
Released_DT = a date - 09/03/2015 or 09/02/2015
Date2 = returns another date value in this case 09/03/2015
What I'm trying to do is: count distinct number of people (Fields!Name.Value) if the Relased_DT = Date2.My IIF statement is returning a zero value.
View 4 Replies
View Related
Mar 16, 2007
Hi folks,Can anyone enlighten me here? I'm trying to use a SPROC which, when supplied with an int, looks up the table and returns certain columns from it. I'm using a SqlCommand, here's my codebehind: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SqlCommand dataSource = new SqlCommand("retrieveData", new SqlConnection(dbConnString)); dataSource .CommandType = CommandType.StoredProcedure; dataSource .Parameters.AddWithValue("id", poid); dataSource .Parameters.AddWithValue("title", title).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("creator", creator).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("assignee", assignee).Direction = ParameterDirection.Output; etc, etc... And the SPROC:------------------------------------------------------------------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[retrieveData] @id int, @title varchar(50) OUTPUT, @creator varchar(50) OUTPUT, @assignee varchar(50) OUTPUT, @contact varchar(50) OUTPUT, @deliveryCost numeric(18,2) OUTPUT, @totalCost numeric(18,2) OUTPUT, @status tinyint OUTPUT, @project smallint OUTPUT, @supplier smallint OUTPUT, @creationDateTime datetime OUTPUT, @amendedDateTime datetime OUTPUT, @locked bit OUTPUT AS /**SET NOCOUNT ON; **/ SELECT [title] AS [@title], [datetime] AS [@creationDateTime], [creator] AS [@creator], [assignee] as [@assignee], [supplier] as [@supplier], [contact] AS [@contact], [delivery_cost] AS [@deliveryCost], [total_cost] AS [@totalCost], [amended_timestamp] AS [@amendedDateTime], [locked] AS [@locked] FROM purchase_orders WHERE [id] = @id; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ The id being passed in is definately not null, and is set to a value of an item I know exists. The resulting error is:
Exception Details: System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.Line 63: retrievePODetails.Connection.Open();Line 64: retrievePODetails.ExecuteNonQuery();[InvalidOperationException: String[1]: the Size property has an invalid size of 0.] System.Data.SqlClient.SqlParameter.Validate(Int32 index) +717091... ... Can anyone see anything I'm missing? Thanks,Ally
View 1 Replies
View Related
Nov 14, 2007
Using C#, SQL Server 2005, ASP.NET 2, in a web app, I've tried removing the size from parameters of type NCHAR, NVARCHAR, and VARCHAR. I'd rather just send a string and let the size of the parameter in the SP truncate any extra chars if need be. I began getting the error below, and eventually realized it happened only with output parameters, as in the code snippet below.String[3]: the Size property has an invalid size of 0. par = new SqlParameter("@BusinessEntity", SqlDbType.NVarChar); par.Direction = ParameterDirection.Output; cmd.Parameters.Add(par); cmd.ExecuteNonQuery();What's the logic behind this? Is there any way around it other than either finding out what the size should be, or assigning a size larger than would ever be needed? ThanksMike Thomas
View 6 Replies
View Related
Jul 25, 2007
I have one db test with one .mdf and .ldf file...mdf file size is 100mb and for some reson i removed all the tablesfrom that .mdf file and transfer it into new secondary file so all thetables moved into secondary file now i want to reduce the first .mdffile from 100 mb to 50mb is that possible,it's showing 90mb is free.Please reply
View 1 Replies
View Related
Jan 24, 2006
I want to know encrypted data's size for designing database field size.
For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.
I think the size does not depend to PassPhrase char length.
Regards,
Yoshihiro Kawabata
View 3 Replies
View Related
Dec 11, 2007
I am getting error to run stored procedure using executenonquery method. The Stored Procedure is having OUTPUT parameter.
ExecuteNonQuery statement is called using SqlHelper.
Error : String[18]: the Size property has an invalid size of 0
View 1 Replies
View Related
Jul 23, 2005
Just wanted to know what is a general rule of thumb when determining log file space against a database's data file.We allow our data file for our database to grow 10%, unlimited. We do not allow our log file to autogrow due to a specific and poorly written process (which we are in a three month process of remove) that can balloon the log file size.Should it be 10% of the Data file, i.e. if the Date file size is 800MB the log file should be 8MB?I realize there are a myraid of factors that go against file size but a general starting point would be nice.ThanksJeff--Message posted via http://www.sqlmonster.com
View 4 Replies
View Related
Apr 20, 2015
The Tabular model is showing 19 GB on disk, but it is acquiring around 40 GB in memory.
View 3 Replies
View Related
Apr 5, 2007
Hi every body...
I have a probleme
I have a web Services which contains a method getValue(IDEq (int), idIndicator(int), startTime(dateTime), endTime(dateTime))
I need to call this method. But my problem is how pass parameter ?
I see the tab Param but it isn't work as I wait,... maybe I do a mistake...
I want that statTime and endTime are select by the user via a calendar for example...
now idIndicator and idEq was result of an other dataSet from a xml datasource...
But I don't how integrate dynamically... I try to enter a parameter via the param tab, and create and expression :
=First(Fields!idEq.Value, "EquipmentDataSet")
but when i execute the query, the promter display <NULL>...
So I don't know how to do and if it is possible !
I hope someone can help me !
Thank you !
View 3 Replies
View Related
Dec 3, 2007
Hi experts,
I'm not sure my design is normal or not. Please give me some advice.
I've a dataset and query by a field name 'companyid'.
select * from companyid where companyid = @icompany which @icompany is a input field.
if user select all, i'll send 0 to @icompany then I need to select all records.
question 1. How can I get all records? (i think about this query select * from companyid <> 0)
if user select for example companyid = 1, i'll send 1 to @icompany and the query work fine.
question 2. How can I change the query to adopt this 2 condition?
Thanks a lot,
Jeff
View 8 Replies
View Related
Feb 15, 2000
Hi,
An MSSQL DB running SAP indicates a smaller DB size (MMC & SAP) than the actual physical size. The difference is about 8 GB.
A lot of records were deleted before this. Did they remain in the DB as NULL values or something ?
Does anyone know what the reason for this could be ? And how to clean this up ?
Thanks in advance,
Paul
View 2 Replies
View Related
Mar 2, 2008
Hi,
i use this script that show me the size of each table and do the sum of all the table size.
SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup.
example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when
i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
THX
View 5 Replies
View Related
Apr 14, 2015
Here are my scenarios:
We have an application with replicated environment setup on sql server 2012 . Users will have a replica on their machines and they will replicate to the master database. It has 3 subscriptions subscribed to the publications on the master db.
1) We set up a replica(which uses sql server 2012) on a machine with no sql server on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 33gigs and ldf has grown to 41 gigs. I went to sql server management studion . Right click and checked the properties of the local database. over all size is around 84 gb with little empty free space available.
2) We set up a replica(which uses sql server 2012) on a machine with sql server 2008 on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
3) We set up a replica(which uses sql server 2012) on a machine with sql server 2012 on it. We have dropped the local database and recreated the local db and did the initial synchronization using replmerge tool. The mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
Why it is allocating the space differently? This is effecting our initial replica set up times.
View 0 Replies
View Related